How to properly do a bulk copy of tables that contains identity columns

I have been struggling with this for a couple of hours now. Now matter what options I set in the Microsoft SQL Server 2005 Import and Export Wizard, I cannot manage it to correctly transfer my identity columns.

It turns out that this is a bug in SQL Server 2005. There are a couple of proclaimed workarounds like this and this. However that didn’t work for me. I cannot uncheck the “Optimize for Many Tables”, because if I do, I get a lot of other key errors when the package runs.

So I stumbled upon this utility, the Simple SQL Bulk Copy.

This is and image that shows a screen shot of the Simple SQL Bulk Copy utiliy.

It worked great! My tables containing identity columns was transferred correctly.

This post is licensed under CC BY 4.0 by the author.