SQL Server Generate Scripts with Identity Insert

sql-server-2008 identity-insert generate-scripts
Adam Levitt picture Adam Levitt · Oct 2, 2012 · Viewed 8.6k times · Source

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

Answer

SqlZim picture SqlZim · Dec 25, 2016

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More