Search code examples
sqlsql-serveropenrowset

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: The solution to the problem lies in the format file "items.fmt" (credits to @serverSentinel) Use \r\n to terminate the line.

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

  • 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.