I am trying to load a large amount data in SQL server from a flat file using BULK INSERT. However, my file has varying number of column, for instance the first row contains 14 and the second contains 4. That is OK, I just want to make a table with the max number of columns and load the file into it with nulls for the missing columns. I can play with it from that point. But it seems that SQL Server when reaching the end of the line and having more columns to fill for that same row in the destination table, just moves on to the next line and attempts to put the data on that line to the wrong column of the table.

Is there a way to get the behavior that I am looking for? Is there a table hint that I can use the specify this? Has anyone run into this before?

Here is the code

BULK INSERT #t
FROM '<path to file>'
WITH 
(
  DATAFILETYPE = 'char',
  KEEPNULLS,
  FIELDTERMINATOR = '#'
)