The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an
IDENTITY column, and (2) make sure that the
IDENTITY starts from the most recent value in the existing rows.
First off, some test data to play with:
CREATE TABLE dbo.ident_test (
id int NOT NULL,
xyz varchar(10) NOT NULL,
CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
The goal is to make the table's primary key column,
IDENTITY column that will start at 21 for the next record that gets inserted. For this example, the column
xyz represents all of the table's other columns.
Before you do anything, please read the warnings at the bottom of this post.
First off, in case something goes wrong:
Now, let's add a temporary work column,
id_temp and set that column to the existing
id column's values:
ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;
Next, we need to drop the existing
id column (you can't just "add" an
IDENTITY to an existing column, you have to create the column as an
IDENTITY). The primary key also has to go, because the column depends on it.
ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;
... and add the column again, this time as an
IDENTITY, along with the primary key:
ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);
Here's where it gets interesting. You can enable
IDENTITY_INSERT on the table, which means that you can manually define the values of an
IDENTITY column when you're inserting new rows (not updating existing rows, though).
SET IDENTITY_INSERT dbo.ident_test ON;
With that set,
DELETE all the rows in the table, but the rows that you're deleting are
OUTPUT right into the very same table - but with specific values for the
id column (from the backup column).
DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);
Once, done, turn
IDENTITY_INSERT back off again.
SET IDENTITY_INSERT dbo.ident_test OFF;
Drop the temporary column that we added:
ALTER TABLE dbo.ident_test DROP COLUMN id_temp;
And finally, reseed the
IDENTITY column, so the next record's
id will resume after the highest existing number in the
DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)
Checking the example table, the highest
id number is 20.
SELECT * FROM dbo.ident_test;
Add another row and check its new
INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;
In the example, the new row will have
id=21. Finally, if you're happy, commit the transaction:
This is not a trivial operation, and it carries quite a few risks that you should be aware of.
Do this in a dedicated test environment. Have backups. :)
I like to use
BEGIN/COMMIT TRANSACTION because it prevents other processes from messing with the table while you're in the middle of changing it, and it gives you the possibility to roll everything back if something goes wrong. However, any other process that tries to access your table before you've committed your transaction will end up waiting. This may be pretty bad if you have a large table and/or you are on a production environment.
OUTPUT .. INTO won't work if your target table has foreign key constraints or any of a number of other features that I can't remember off the top of my head. You could instead off-load the data into a temporary table instead, and then insert it back into the original table. You might be able to use partition switching (even if you don't use partitions).
Run these statements one by one, not as a batch or in a stored procedure.
Try to think of other things that may depend on the
id column that you're dropping and re-creating. Any indexes will have to be dropped and re-created (like we did with the primary key). Remember to script every index and constraint that you will need to recreate beforehand.
DELETE triggers on the table.
If re-creating the table is an option:
If re-creating the table is an option for you, everything is a lot simpler:
- Create the empty table, with the
id column as an
IDENTITY_INSERT ON for the table,
- Populate the table,
IDENTITY_INSERT OFF, and
- Reseed the identity.