Systems Engineering and RDBMS

Updating Identity Values in a table

Posted by decipherinfosys on January 8, 2009

We have covered identity related posts several times in our blog posts – you can search for it and get the salient points regarding the usage of identity, scope_identity(), IDENTITY_INSERT etc. In a recent question by one of our readers, he had asked whether one can update identity columns in a table – regardless of whether they have a primary key constraint on them or not.

SQL Server does not allow updating the identity values. Let’s pick an example and see how we can achieve this:

USE DECIPHERTEST
GO
CREATE TABLE IDENTITY_TEST (COL1 INT IDENTITY, COL2 INT);
GO
INSERT INTO IDENTITY_TEST (COL2) VALUES (10);
INSERT INTO IDENTITY_TEST (COL2) VALUES (20);
INSERT INTO IDENTITY_TEST (COL2) VALUES (30);
INSERT INTO IDENTITY_TEST (COL2) VALUES (40);

And now, if you fire off a simple update statement:

UPDATE IDENTITY_TEST SET COL1 = 5 WHERE COL1 = 4

You will get an error:

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘COL1’.

In order to be able to do the update, you would need to simulate a delete and an insert and make use of the “SET IDENTITY_INSERT <table_name> ON” command that we have also covered before. Here is how we can do this in the case of a standalone table:

/*Declare the table variable or a temp table*/
declare @table table (col1 int, col2 int)
insert into @table (col1, col2) select col1, col2 from identity_test;
update @table set col1 = 5 where col1 = 4;
truncate table identity_test;
set identity_insert identity_test on
insert into identity_test (col1, col2) select col1, col2 from @table
set identity_insert identity_test off

select * from identity_test

COL1        COL2
----------- -----------
1           10
2           20
3           30
5           40

And of course, you should put the above in a TRY…CATCH block with proper error handling. You can read more on that here. Do note that in the above example, it was a standalone table and we used truncate which re-sets the identity value back to 1. In real life scenarios, you might have to do this with tables involved in referential constraints, you can then use the script to disable/enable the constraints and use delete instead of truncate. You would then also need to use DBCC CHECKIDENT in order to re-seed the value back – this is to take into account the scenarios where your update might result into difference in identity values.

One Response to “Updating Identity Values in a table”

  1. Is’t possible without creating the temp table. Because it’s a long time process if there any different ways kindly post pls…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: