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
- Take this a step further to run this for each of your tables or a batch of tables
- Add this script to your SQL toolbox
- Refer to the previous tip – Using Identity Insert to keep SQL Server database table keys in sync
