When it comes to moving data between tables in SQL Server, one common challenge is dealing with identity columns. These columns are often used to create primary or unique keys on a table, but they can complicate the process of transferring data from one table to another.

In a previous tip, we discussed how to use the IDENTITY_INSERT command to keep identity values in sync when moving data between like tables. However, this approach requires manually specifying each column for the INSERT and SELECT statements, which can be time-consuming and error-prone.

In this tip, we will explore a simple way to automate the creation of the INSERT command using the system metadata. This approach can save you time and effort, especially when dealing with multiple tables or an entire database that needs to be migrated.

Let’s consider a scenario where we have two tables, Employee and Employee2, with identical structures. The Employee table has an identity column named empID. Normally, you would need to specify each column for the INSERT and SELECT statements, like this:

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;

While this approach works for a single table with a few columns, it becomes cumbersome when dealing with multiple tables or tables with many columns. To automate this process, we can leverage the system metadata to generate the INSERT command dynamically.

Here’s an example script that demonstrates this approach:

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;';

PRINT @sqlcmd;

This script uses a cursor to iterate over the columns of the source table (Employee) and builds the column list dynamically. It then generates the INSERT command using the schema and table names provided.

By running this script, you will get the generated INSERT command as output. You can then execute this command to migrate the data from the source table to the destination table while preserving the identity values.

Automating the generation of the INSERT command using system metadata can greatly simplify the process of moving data between tables with identity columns. This approach eliminates the need to manually specify each column, saving you time and reducing the risk of errors.

Next time you need to migrate data in SQL Server, consider using this automated approach to streamline the process and ensure the integrity of your identity columns.