Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Auto create identity insert SQL Server command to sync tables


By:   |   Updated: 2010-06-10   |   Comments (2)   |   Related: More > Identities

Problem

In a previous tip, Using Identity Insert to keep SQL Server database table keys in sync, we discussed how to move data between like tables when the table has an identity column.  The biggest drawback when doing this is that you need to specify each column of the table for the INSERT and also make sure the columns on the SELECT match up as well. Doing this for one or two tables is not that hard, but what if you have several tables or an entire database that you need to move and all of the tables have identity columns?  In this tip we take a look at a simple way to create the INSERT command using the system meta data. 

Solution

Identity columns are often used in tables as a way to create a primary or unique key on a table.  When moving data between like tables that have identity columns you need to specify each column for the INSERT and the SELECT. 

Let's say we have a table named Employee with the following structure and another table named Employee2 with the same structure:

CREATE TABLE [dbo].[Employee](
 [empID] [int] IDENTITY(1,1) NOT NULL,
 [fname] [varchar](50) NULL,
 [lname] [varchar](50) NULL,
 CONSTRAINT [PK_dbo.Employee] PRIMARY KEY CLUSTERED 
(
 [empID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

You can not do a simple command such as the following to move the data:

SET IDENTITY_INSERT dbo.Employee2 ON
INSERT INTO dbo.Employee2
SELECT * FROM dbo.Employee
SET IDENTITY_INSERT dbo.Employee2 OFF

If you run the above command and the table has an identity column you will get this error message:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.Employee2' can 
only be specified when a column list is used and IDENTITY_INSERT is ON.

The solution to get around this is that you need to specify the columns as follows:

SET IDENTITY_INSERT dbo.Employee2 ON
INSERT INTO dbo.Employee2 (empID, fname, lname)
SELECT * FROM dbo.Employee
SET IDENTITY_INSERT dbo.Employee2 OFF

The above example is pretty simple to put together if you only need to do one table and only have a few columns, but what if you have a bunch of tables with many columns? 

The following script uses the system meta data to pull the columns and create the command for you instead of having to cut and paste or type the entire command.  For this script you need to specify the schema and table names for the current and new table as shown below.

DECLARE @currentTableName sysname
DECLARE @newTableName sysname
DECLARE @currentSchemaName sysname
DECLARE @newSchemaName sysname
DECLARE @sqlcmd varchar(max)
DECLARE @colname sysname
DECLARE @collist varchar(max)
SET @currentTableName = 'Employee'
SET @newTableName = 'Employee2'
SET @currentSchemaName = 'dbo'
SET @newSchemaName = 'dbo'
SET @collist = ''
DECLARE colCursor CURSOR FOR 
select c.name from sys.all_columns c
inner join sys.all_objects o
on c.object_id = o.object_id
where o.type = 'U'
and o.name = @currentTableName
and o.schema_id = schema_id(@currentSchemaName)
ORDER BY column_id
OPEN colCursor   
FETCH NEXT FROM colCursor INTO @colname   
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @collist = @collist + '[' + @colname + ']'
       FETCH NEXT FROM colCursor INTO @colname   
       IF @@FETCH_STATUS = 0 
   SET @collist = @collist + ','
END   
CLOSE colCursor   
DEALLOCATE colCursor 
SET @sqlcmd = 'SET IDENTITY_INSERT [' + @newSchemaName + '].[' + 
@newTableName + '] ON;' 
SET @sqlcmd = @sqlcmd + 
'INSERT INTO [' + @newSchemaName + '].[' + @newTableName + '] ('
+ @collist + 
')
SELECT ' + @collist + 
' FROM [' + @currentSchemaName + '].[' + @currentTableName + '];'
SET @sqlcmd = @sqlcmd + 'SET IDENTITY_INSERT [' + @newSchemaName + 
'].[' + @newTableName + '] OFF;' 
--EXEC (@sqlcmd)
PRINT @sqlcmd

If we run the above code we get the below command that can then be run.  Note in the above code that this does a PRINT of the commands instead of executing the code. If you want to execute the code just uncomment the EXEC (@sqlcmd) line of the code and this will create and execute the command.

SET IDENTITY_INSERT [dbo].[Employee2] ON;
INSERT INTO [dbo].[Employee2] ([empID],[fname],[lname])
SELECT [empID],[fname],[lname] FROM [dbo].[Employee];
SET IDENTITY_INSERT [dbo].[Employee2] OFF;

That's all there is to it. 

Next Steps


Last Updated: 2010-06-10


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, October 04, 2018 - 5:43:53 PM - Ronald Rex Back To Top

What if I had a client application and I wanted to grab a number that would fill in the gaps, or get the next number in the sequence. when a record is deleted, or just before the client application inserts a new record Can you think of how I could implement this? Thanks ! 

 


Tuesday, May 02, 2017 - 12:23:03 PM - Don Swanson Back To Top

 Worked perfectly. Thanks!

 


Learn more about SQL Server tools