CODEWITHSUNDEEP
sql-server-2008identity-insertgenerate-scripts
Adam Levitt
Adam Levitt

Reputation: 10476

SQL Server Generate Scripts with Identity Insert

When generating database scripts, I'm scripting data to be migrated to a different environment. Is there a setting in the script generation that I can enable to set IDENTITY_INSERT on/off automatically so I don't have to go through each table manually in the generated script and set it? I'm using SSMS and I'd like to do this via SSMS.

Here's what I am getting:

INSERT my_table (my_table_id, my_table_name) VALUES (1, 'val1');
INSERT my_table (my_table_id, my_table_name) VALUES (2, 'val2');

Here's what I want:

SET IDENTITY_INSERT my_table ON
INSERT my_table (my_table_id, my_table_name) VALUES (1, 'val1');
INSERT my_table (my_table_id, my_table_name) VALUES (2, 'val2');
SET IDENTITY_INSERT my_table OFF

Upvotes: 2

Views: 11089

Answers (2)

SqlZim
SqlZim

Reputation: 38023

I know this is an old question, but the accepted answer and the comments to the accepted answer aren't quite correct regarding SSMS.

When using the generate scripts task in Sql Server Management Studio (SSMS) to generate scripts with data, set identity_insert statements will be included for tables that have an identity column.

In the object explorer: Tasks -> Generate Scripts -> [All Tables or selected tables] -> Advanced -> [Schema with Data or Data]

  • If the table to script data from does not have a column with the identity property , it will not generate the set identity_insert statements.

  • If the table to script data from does have a column with the identity property , it will generate the set identity_insert statements.

Tested & Confirmed using SSMS 2008 & SSMS 2012

In the OP's situation, I'm guessing the origin table did not have the identity property set for my_table_id in the source table, but the identity property was set for my_table_id in the destination table.

To get the desired output, change the table to script data from to have my_table_id to have the identity property.

This article explains in depth the steps to do this (without using the designer in SSMS): Add or drop identity property for an existing SQL Server column - Greg Robidoux

  • Create a new column with the identity property

  • Transfer the data from the existing id column to the new column

  • Drop the existing id column.

  • Rename the new column to the original column name

Upvotes: 7

Randy Minder
Randy Minder

Reputation: 48402

You didn't say what tool you are using to generate your scripts, but if you have a tool like Red-Gate Data Compare, it will generate these statements for you, if you include the auto-increment field in the comparison. I'm not aware that SSMS has any such option.

If you have Visual Studio Premium or Ultimate edition, then you also have access to DBPro (Database Professional) that has a data compare and synchronization option. I believe this will generate the IDENTITY_INSERT statements for you as well.

Upvotes: 1

Related Questions