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.