Search code examples
sql-serverdata-import

How to enable Identity Insert for multiple tables at once?

I'm using the SQL Server 2014 Import and Export Wizard to copy data from one SQL Server instance to another.

I have 8 databases, each with 63 tables, so 504 tables in total.

The Import/Export works just fine, data flows across into the destination database. The issue I have is that I need the Enable Identity Insert option for every table.

I need Enable Identity Insert defaulted to ticked/selected, otherwise I'm going to be here all day just clicking Edit mappings... > Enable Identity Insert > OK... Move down... Edit mappings... > Enable Identity Insert > OK... Move down... Edit mappings... > Enable Identity Insert > OK...

You get the idea...

Is there any way to set the default for Enable Identity Insert..?

Solution

  • Found this which answers my question:

    https://dba.stackexchange.com/questions/190119/how-to-set-enable-identity-insert-for-all-the-tables-at-once-during-importing

    You can do it...

    You select multiple rows, then the Edit mappings... button leads to a different dialog box, where you can set options for all tables. The answer above has a nice print-screen showing this.

    Sounds simple, but I think I missed it because the interface has two selection methods on one form:

    1. Tick box selection - for including the source table in the data transfer
    2. Highlight row selection - for selecting multiple rows to set options