2

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?

  • Do you need to merge the test data into production, or replace what's in production with what's in test? – S M Jul 10 '17 at 19:49
  • Is this a one time 'fix' or will it need to happen all the time? If a one time deal then the answer below sounds like a good option, but if not I'd really look into your requirements as you could be inviting trouble. – Jeff A Jul 10 '17 at 20:24
  • I would say we should replace with what is in production, as if anything has been deleted we don't want it to remain in production. Also, this should be happening regularly as new apps get deployed that use the same database. – Marcel Marino Jul 10 '17 at 20:27
  • A couple of thoughts - if you can drop the table and copy it again, then you don't have referential integrity between tables, in which case, you may have already have orphaned data (you're not going to corrupt your database, in the purest sense of the word). Second, given that this is a test environment, is there anything keeping you from simply restoring a production backup and overwriting the entire database? If it's a true test environment, that should be your standard process for synchronizing with production. – mathewb Jul 10 '17 at 20:39
  • It's . . . partial production. We keep building new apps that reuse old tables and create new tables, so those new tables need to be added and moved over, and external clients use the application, add data, ask for changes, etc. Our real test system is only available to internal users, so we have a database that is only partially production. This is why a full restore doesn't work. Also, if we drop and readd tables, we would do the same to any connected tables as well. This is mostly just for user auth stuff. – Marcel Marino Jul 10 '17 at 21:02
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.