Cyberpau Cyberpau - 2 months ago 11
SQL Question

Can't multiple insert using openrowset

I already searched a couple of times. Some post are related but still can't help me on this problem.

Here are the sample items of my items.txt. I checked the .txt file and there is absolutely no white space, etc.

0000100000
7005432111
4545213695
4545213612
0000100001
0000100002


So here's my code so far:

INSERT INTO items(id, customerID)
SELECT items.id , C.customerID
FROM OPENROWSET(BULK N'C:\items.txt', FORMATFILE='C:\items.fmt') AS items
LEFT JOIN customerTable AS C ON items.id = C.id


And it returns this values:

0000100000 NULL
7005432111 NULL
4545213695 NULL
4545213612 NULL
0000100001 NULL
0000100002 NULL


It return NULL values in the customerID column, wherein there should be some data there. I think the problem is on
items.id = C.id
it cannot read each values from items.txt but when I use this code:

INSERT INTO items(id, customerID)
SELECT items.id , C.customerID
FROM OPENROWSET(BULK N'C:\items.txt', FORMATFILE='C:\items.fmt') AS items
LEFT JOIN customerTable AS C ON C.id = '0000100000'


It returns this:

0000100000 2
7005432111 2
4545213695 2
4545213612 2
0000100001 2
0000100002 2


Thanks!

EDIT:
here is the content of items.fmt

10.0
1
1 SQLCHAR 0 46 "\n" 1 loyaltyID SQL_Latin1_General_CP1_CI_AS

Answer

Make absolutely sure of:

1) the items.txt is in UNIX line endings (\n) not Windows (\r\n). This is most likely your problem.

Try this format to verify or use a text editor that can set the line mode

10.0
1
1       SQLCHAR             0       46      "\r\n"     1     loyaltyID                    SQL_Latin1_General_CP1_CI_AS

2) The loyaltyID column is being read in as a char(46) SQL_Latin1_General_CP1_CI_AS. Check the data types for length, and definition. You may be having difficulties comparing an integer field to a char field. cast appropriately. Less likely, check ansi_padding setting to make sure your varchar to char comparisons aren't comparing extra spaces. Check your collations. for example SQL_Latin1_General_CP1_CI_AS is much different than latin1_general_bin.

If this doesn't solve your question, please post the schema for customerTable and some sample records.

Comments