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.

How to bulk export multiple tables from SQL Server

Maybe you have been in the same situation as I ? You need to bulk export multiple tables from SQL Server, and can’t find any option in the Import and Export Wizard within SQL Server 2005. At least I couldn’t find any out of the box functionality to do this. So I went ahead and wrote a small T-SQL script that uses the BCP command utility to perform this desired task.

 

SQL Script – Copy Code

If you try to run this on a SQL Server 2005, you may encounter this error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component
is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.
For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

In that case you have to unlock the possibility to execute xp_cmdshell usi
ng this script:

SQL Script – Copy Code