2

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) ?

1

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!

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.