Sql-server – Inserting rows into other table whilst preserving IDENTITY

etlidentitysql serversql-server-2008t-sql

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?

Best Answer

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.

Related Question