TL;DR; BULK INSERT doesn’t have an easy way to specify a column list so the columns in the insert file must match the columns in the table unless you use a format file or a staging table.

As simple as they appear on the surface identity columns are fairly complicated things. BULK INSERT, on the other hand, is pretty complicated even on the surface (at least as far as I’m concerned). Because of this, the combination can get even worse. When you do an insert into a table that has an identity column you usually just ignore that column and it’s automatically filled in. However, that’s not true with BULK INSERT. Unless you use a format file, the number of columns (and their order) in the file has to match the table. So for example, if you have a text file like this:

And run the following:

CREATE TABLE BulkInsertIdentFirst (
Id INT NOT NULL Identity(1,1), 
Col1 varchar(20));
GO
BULK INSERT BulkInsertIdentFirst FROM 
'C:\Users\kenne_000\OneDrive\Documents\Blog Stuff\BulkInsertLoad.txt';

You get this error:
Msg 4832, Level 16, State 1, Line 11

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 11

The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 11

Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

But Ken I hear you cry, there’s a KEEPIDENTITY clause in the BULK INSERT command. Surely BULK INSERT must allow for identity columns? Well, yes and no. Let’s say we change the import file to look like this:

BULK INSERT BulkInsertIdentFirst FROM 
'C:\Users\kenne_000\OneDrive\Documents\Blog Stuff\BulkInsertLoad.txt'
WITH (FIELDTERMINATOR = ',');

(5 row(s) affected)

Now, if I add values to the identity column in the text file it gets interesting:

Run the same command and I’d expect an error. I mean we are trying to insert a value into the identity column right? But nope.

(5 row(s) affected)

The identity column just completely ignored the values I was trying to put into it. They have to be of a matching data type (an integer in this case) but otherwise completely ignored.

I just need to use KEEPIDENTITY if I want it to actually use the values I put in the file.

BULK INSERT BulkInsertIdentFirst FROM 
'C:\Users\kenne_000\OneDrive\Documents\Blog Stuff\BulkInsertLoad.txt'
WITH (FIELDTERMINATOR = ',', KEEPIDENTITY);

And in case you were wondering, I tried similar tests where I put the identity column at the end of the table. Same results.

I’ve decided that for me at least, creating a staging table that matches the import file is the easiest way to deal with bulk inserts like this.