How to set 'Enable identity insert' for all the tables at once during importing data in SQL Server?

Question

I am trying to copy a database from a remote server. I don't have access to its backups or file system. My destination database on my dev machine needs to get all the data plus the same tables structures including primary keys with identity settings and the rest of the constraints.

The Import/Export facility has the option to set the identity insert as shown below but I have to do this manually per table. I have over 300 tables to copy and most of them have identity primary keys. This is going to be very painful process doing the copy multiple times.

Is there a way to set this identity insert setting all at once for all the tables? Or some other reliable way of copying databases in such a setup? I am using SSMS 7.3 against SQL Server 2014.

Answer 1

You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.

Answer 2

You can use Dynamic sql to set it for all tables described here. If the db is large, then I would suggest you to script out schema and create the shell / skeleton DB and then use bcp out and bulk insert data into it. You can automate this using powershell or sql agent job.

ALternatively, you can just kick off a copy_only backup and use copy-dbadatabase (part of dbatools - powershell based) to backup and restore the db from source to destination.

Answer 3

I think you can do what you want by creating the import package using OLE DB source/destination components with the default identity insert. Save it to a package file instead of executing immediately. Open the package source in a text editor and globally change the FastLoadKeepIdentity property to true all destination tables. Then manually changes it to false for those tables without identity columns.

Below is an xml fragment of the package source that needs to be changed.

        <property
          dataType="System.Boolean"
          description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on."
          name="FastLoadKeepIdentity">true</property>

Save the modified package and then run it using the dtexec.exe command-line utility or the dtexecui.exe graphical tool. That will allow you to easily repeat the copy without going through the import/export wizard again.

Answer 4

I used Toad to create the insert statements. It's a commercial product and has a free trial.