Start identity in an existing table / column from a particular number

Question

I have an existing table with thousands of records, with primary key, foreign key and relationships. I have to set the primary key column to be identity, but I don't want to start it from 0 because the table has existing records, and I also don't want to lose data.

I must to save the exact table design.

I have to add an identity, and reseed it. What should I do?

Thank you!

Does this answer your question? [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) — Serg, Apr 26 '21 at 07:07

score 1 · Answer 1 · answered Apr 26 '21 at 06:57

1

Just update table with IDs. Then Choose current max ID to execute

DBCC CHECKIDENT ('MyTable', RESEED, maxId)

More about reseeding

answered Apr 26 '21 at 06:57

Michał Turczyn

32,028
14
47
69

Thanks. but this column is not an identity now, just a primary key. Would this work well? – ut or Apr 26 '21 at 07:07
1

@utor You need to drop table and cache its data, recreate it with desired values and then populate with cached data. – Michał Turczyn Apr 26 '21 at 07:21

Venkataraman R · Answer 2 · 2021-04-26T11:18:50.000

You cannot add identity to existing table. Identity has to be specified as part of table creation. You have two options.

Recreate new table with identity properly set up with reseed value & Set up IDENTITY_INSERT ON & Insert existing rows to new table & IDENTITY_INSERT OFF.

CREATE TABLE [dbo].[NewTable](
    [ID] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY, -- 1000 is Reseed value
    Col1
    Col2
);

-- SET IDENTITY_INSERT to ON.  
SET IDENTITY_INSERT dbo.NewTable ON;  
GO  
INSERT INTO NewTable(Id,col1,col2...)
SELECT Id, col1, col2... FROM oldtable;
GO
SET IDENTITY_INSERT dbo.NewTable OFF;  
GO

Create a sequence with starting value as the reseed value and use the sequence for your future insertions.

CREATE SEQUENCE dbo.TestSeq
    START WITH 1000 -- Reseed value  
    INCREMENT BY 1 ;  
GO 

INSERT Test.TestTable (ID, Col1, Col2,...)  
    VALUES (NEXT VALUE FOR dbo.TestSeq, 1, 2,...) ;  
GO

ut or · Accepted Answer · 2021-04-26T09:32:15.110

0

I found the solution:

At SSMS -> Tools -> Options -> Designers: Remove the V from 'Prevent Saving changes that require table re-creation'.
Open the table in Design Mode, and add the V to the Identity Spcification property.

This will also reseed it automatically.

edited Apr 26 '21 at 09:32

answered Apr 26 '21 at 09:16

ut or

46
10

Start identity in an existing table / column from a particular number

3 Answers3