sql - Can't multiple insert using openrowset -

i searched couple of times. post related still can't me on problem.

here sample items of items.txt. checked .txt file , there absolutely no white space, etc.

0000100000 7005432111 4545213695 4545213612 0000100001 0000100002 

so here's code far:

insert items(id, customerid) select items.id , c.customerid     openrowset(bulk n'c:\items.txt', formatfile='c:\items.fmt') items     left join customertable c on items.id = c.id 

and returns values:

0000100000  null 7005432111  null 4545213695  null 4545213612  null 0000100001  null 0000100002  null 

it return null values in customerid column, wherein there should data there. think problem on items.id = c.id cannot read each values items.txt when use code:

insert items(id, customerid) select items.id , c.customerid     openrowset(bulk n'c:\items.txt', formatfile='c:\items.fmt') items     left join customertable c on c.id = '0000100000' 

it returns this:

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

thanks!

edit: solution problem lies in format file "items.fmt" (credits @serversentinel) use \r\n terminate line.

10.0 1 1       sqlchar             0       46      "\r\n"     1     loyaltyid                    sql_latin1_general_cp1_ci_as 

make absolutely sure of:

1) items.txt in unix line endings (\n) not windows (\r\n). problem.

try format verify or use text editor can set line mode

10.0 1 1       sqlchar             0       46      "\r\n"     1     loyaltyid                    sql_latin1_general_cp1_ci_as 

2) loyaltyid column being read in char(46) sql_latin1_general_cp1_ci_as. check data types length, , definition. may having difficulties comparing integer field char field. cast appropriately. less likely, check ansi_padding setting make sure varchar char comparisons aren't comparing spaces. check collations. example sql_latin1_general_cp1_ci_as different latin1_general_bin.

if doesn't solve question, please post schema customertable , sample records.