Inserting rows into other table whilst preserving IDENTITY

Question

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source table, I'm doing an insert on those rows in the target table. The schema defines one of the columns as an identity column. So SQL Server auto increments the id for new rows that are inserted. Because I'm moving existing ids, I need to remove the identity, insert those rows, then re-apply the identity, then reset the seed so it matches the source table. How can this be done programmatically?

1. Can I remove the identity declaration from an existing column?
2. Can I mark an existing column as an identity?
3. How can I reset the seed for an identity column?

Answer 1

While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table:

 SET IDENTITY_INSERT dbo.tablename ON;

 INSERT ...

 SET IDENTITY_INSERT dbo.tablename OFF;

 DECLARE @sql NVARCHAR(MAX);

 SELECT @sql = N'DBCC CHECKIDENT(''dbo.tablename'', RESEED, '
   + RTRIM(MAX(id_column_name)) + ');' FROM dbo.tablename;

 EXEC sp_executesql @sql;

I'm not sure what your best course of action would be to correct duplicates. If you insert 1000 new rows after reseeding, it is likely that the source system will generate new identity values that will conflict. What you might consider doing is simply setting one of the identity values to generate numbers well above the range that the other table won't ever get to (say 1 billion). You can still use IDENTITY_INSERT to merge, but there will never be a conflict. This also makes it very easy to determine whether a row was generated locally or imported.

Answer 2

After a quick test, but I could not alter a column to remove the identity property. I assumed this would work but it does not appear to be the case.

ALTER TABLE dbo.FOO 
ALTER COLUMN myId int;

You cannot add an identity property to an existing column. In SQL Server 2012, you can use a SEQUENCE object to effect the same behaviour but it won't be an identity

-- does not work
ALTER TABLE dbo.FOO 
ALTER COLUMN myId int identity(1,1);

To reset the identity value, you will be interested in DBCC CHECKIDENT with RESEED

DBCC CHECKIDENT ("dbo.FOO", RESEED, 123456);

All that said, you don't have to remove the identity property at all in your scenario. Simply set the IDENTITY_INSERT on the target table, load data and when you're finished, reseed it to match your source table.

Answer 3

Do you really really need to match the id from the table you are merging after the load? Or do you only need it to get child records in and properly related? If it is only used to get child tbales properly related, then this a process we have used on occasion:

Stage all the data in work tables. Add a column for the Id from the other table to the table you are loading to.

Let the process create new records with identities for the parent table.

Update the staged child table to use the new id by joining on the column you added.

Have the process load the child tables.