SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Bulk insert and an identity column

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));
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'

(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'

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...