Sql-server – Existing data to new table, how to set up the PK & Identity

etlidentityprimary-keysql server

I am moving an Access app to SQL Server. I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field. The field is currently set as IDENTITY(1,1). I know I can set the IDENTITY_INSERT for the insert period, but do I then need to adjust the IDENTITY start point so the next insert does not start at 1? Example, the newest row in the existing data is id 4132, so do I insert the data then alter the column to IDENTITY (4133,1) ?

Best Answer

This is simple enough to test on your own. Here's how:

Simple table:

CREATE TABLE dbo.whatever
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Whatever VARCHAR(10)
);

Simple insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on the max Id and current identity value:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 100.

Do an out of band insert:

SET IDENTITY_INSERT dbo.whatever ON;

DECLARE @maxid INT = (SELECT MAX(w.Id) FROM dbo.whatever AS w);

INSERT dbo.whatever ( Id, Whatever )
SELECT x.Id + @maxid, x.Whatever
FROM   (   SELECT TOP 100 ROW_NUMBER() OVER ( ORDER BY @@SPID ) AS Id, 'A' AS Whatever
           FROM   sys.messages AS m ) AS x;

SET IDENTITY_INSERT dbo.whatever OFF;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 200.

Do another insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 300.

Hope this helps!

Related Question