Transferring Auto Incremented Data

Question

Let's say we have two identical tables in two different databases. Each have a primary key field that is auto incremented. For example:

TestDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

ProdDB: Table1    
Key     Value
1       Mike
2       Jane
3       Stephanie

We add some records and delete some records from test, so they no longer match:

TestDB: Table1
Key     Value
1       Mike
4       Jacob
5       Lily
6       Laura

ProdDB: Table1    
Key     Value
1       Mike
2       Jane
3       Stephanie

We need the data on production to match what is on test. Due to auto increment, we can't simply delete all the records and then push them in fresh, their keys would be 4,5,6,7 instead of 1,4,5,6. We can't reseed after, that would make them 1,2,3,4.

We could drop the table and just copy the whole thing over again, but from what I am told doing that too many times could corrupt the database.

I had thought that we could simply turn off auto increment, update the records, and then turn auto increment back on, but my DBA tells me that wouldn't work either. There must be a fairly standard process for moving data between test and production, so what are we missing?

Answer 1

You cannot update an IDENTITY field, so you have to insert new records into the table if you want to manipulate the IDENTITY value. You are able to adjust the IDENTITY SEED throughout the process though to fit your needs or outright ignore it (so long as you do not insert duplicate values) with SET IDENTITY_INSERT [tableName] ON. Here's an example script that hopefully illustrates what you can do better than my explanation:

CREATE TABLE #tmp
(
      ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , MyValue VARCHAR(255)
)

-- Insert Test Data
INSERT INTO #tmp (MyValue)
VALUES ('one')
      ,('two')
      ,('four')
      ,('three')
      ,('five')
      ,('del')
      ,('seven')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('thirteen')

-- Purge some test data to make a pocket of missing IDENTITY values
DELETE FROM #tmp
WHERE MyValue = 'del'

-- Check Current IDENTITY value
DBCC CHECKIDENT(#tmp)

SELECT * FROM #tmp

-- purge ID 3 & 4 to reinsert/fix their IDs
DELETE FROM #tmp WHERE ID IN (3, 4)

SET IDENTITY_INSERT #tmp ON

--Re-Insert 'three' and 'four' records with matching IDs
INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
      ,(4, 'four')
-- Add Values Outside the Range
      ,(21, 'twenty one')
      ,(101, 'one hundred one')
SET IDENTITY_INSERT #tmp OFF

SELECT * FROM #tmp

-- As you can see adding values outside the expected range auto-advances the IDENTITY
DBCC CHECKIDENT(#tmp)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred two')

SELECT * FROM #tmp

DBCC CHECKIDENT(#tmp)

--Advance the Identity to 130
DBCC CHECKIDENT(#tmp, RESEED, 130)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty one')

SELECT * FROM #tmp

--decriment the Identity
DBCC CHECKIDENT(#tmp, RESEED, 5)
INSERT INTO #tmp (MyValue)
VALUES ('six')

SELECT * FROM #tmp

-- caution this will fail as IDENTITY 7 already exists
BEGIN TRY
INSERT INTO #tmp (MyValue)
VALUES ('seven')
END TRY
BEGIN CATCH
    DECLARE @myMessage NVARCHAR(4000)

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

-- caution this will fail as IDENTITY 3 already exists
BEGIN TRY
SET IDENTITY_INSERT #tmp ON

INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
END TRY
BEGIN CATCH

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

SET IDENTITY_INSERT #tmp OFF

-- Be sure to Reset IDENTITY back to max value otherwise you risk an IDENTITY value collision
DECLARE @maxID INT
SELECT @maxID = MAX(ID) FROM #tmp

DBCC CHECKIDENT(#tmp, RESEED, @maxID)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty two')

SELECT * FROM #tmp

-- Cleanup
DROP TABLE #tmp

Some notes, DBCC CHECKIDENT requires db_ddladmin privileges or higher to use. Because of this, I couldn't include a DB Fiddle example. Also, and this may be something you want to dig into as well, but I believe that pushing data from a "test" environment qualifies said environment as "production worthy" which may affect your licensing. Licensing is out of scope, but this could bite you depending on how you have your server licensing setup, so just be wary of promoting data up your environment stack.