Auto create identity insert SQL Server command to sync tables

By:   |   Comments (2)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, October 4, 2018 - 5:43:53 PM - Ronald Rex Back To Top (77851)

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 2, 2017 - 12:23:03 PM - Don Swanson Back To Top (55390)

 Worked perfectly. Thanks!

 















get free sql tips
agree to terms