#Define output variables
$OutputInstance    = "InstanceName"
$OutputDatabase  = "DatabaseName"
$OutputTable         = "LastLogins"
$OutputConnectionString    = "Data Source="+$OutputInstance+"; Integrated Security=True;Initial Catalog="+$OutputDatabase+";"

#Create the bulk copy object
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $OutputConnectionString 

#Define the destination table 
$bulkCopy.DestinationTableName = $OutputTable
#Bulk copy data
$bulkCopy.WriteToServer($myData)

In the first section above I’m declaring and setting the initial values for my variables. I probably don’t need to split it out quite so much for something this simple, but I found that this way it’s easy for me to change any of the pieces, and, more importantly, easier for other people to understand.

Next I create an instance of the SqlBulkCopy object. There are a fair number of properties and methods available and I recommend glancing at the BOL link but here I’m being very simple. I create the object passing in the connection string, set the destination table property, and then use the WriteToServer method to write the contents of $myData (the recordset I created in the previous post) to the destination table. As with any bulk copy the structures have to match and it’s an append, not an overwrite.

Like I said, pretty easy.

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.