Interview Question - How to manually insert a value into Identity column ?

In the last blog entry we saw that how Identity column values cannot be updated. And at the end I had asked you a question what if we still want to do it .. can we ?

The answer is - No we can't. But we can simulate the behavior though. What does that mean? I mean we can do certain operation which would suggest as if we have updated the Identity value..

• Copy the record details for identity value that you want to update ( I am assuming here that you don't have duplicates in the identity column. In case you do then you know the record for which you want to preserve the details. )
• Delete the record for identity value that you want to update
• Insert new record into table with the identity value that you wanted to set for the deleted record

First 2 parts are easy and are no-brainer ... What about 3rd step ?

3'rd step requires us to insert value into Identity column explicitly (manually). And since we are trying to override the default behavior of the generating automatic value.. So let us try it ...

Let me borrow few scripts from the previous blog 

CREATE TABLE #tbl_Identity(
   Id SMALLINT IDENTITY(1,1),
   Name VARCHAR(10)
)

INSERT INTO #tbl_Identity (Name)

SELECT 'India' UNION ALL
SELECT 'UK' UNION ALL
SELECT 'USA' UNION ALL
SELECT 'Canada'

SELECT * FROM #tbl_Identity 

We will try to update the Id value from 4 to 5 for canada.  We have copied the details, now lets delete the record first 

As you can see record has been deleted. let us verify it though 

Now let us try to insert the record with Id = 5 and Name = Canada

INSERT INTO #tbl_Identity (Id,Name) SELECT 5,'Canada'

It fails with an error 

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table (tab name) when IDENTITY_INSERT is set to OFF.

The error suggests that IDENTITY_INSERT must be set to ON.

Let us try to Insert record after setting IDENTITY_INSERT to ON 

SET IDENTITY_INSERT #tbl_Identity ON

INSERT INTO #tbl_Identity SELECT 5,'Canada'

Still it failed with an error 

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table '#tbl_Identity' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Ok .. so how many of you guys noticed that while doing insert this time around I did not specify column names explicitly ? Just to show you this error I did it or did I ? :)

So Error message clearly states that column list be provided.. Let us try with that

SET IDENTITY_INSERT #tbl_Identity ON

INSERT INTO #tbl_Identity (Id,Name) SELECT 5,'Canada' 

It succeeded ...

Let us see .. if we have what we wants ...

SELECT * FROM #tbl_Identity

So now you know how to manually INSERT value into identity column and Effectively How to update it?

Word of caution - make sure to SET IDENTITY_INSERT to OFF for the table otherwise next insert statement where we want Identity to behave normally would fail.

INSERT INTO #tbl_Identity (Name) SELECT 'SA'  

Msg 545, Level 16, State 1, Line 1

Explicit value must be specified for identity column in table 'table name' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.


SET IDENTITY_INSERT #tbl_Identity OFF

INSERT INTO #tbl_Identity (Name) SELECT 'SA'SELECT * FROM #tbl_Identity  

Also notice that the Identity column has taken next value as 6 and not 5. Although last value that was automatically generated by Identity column was 4. This tells us that may be SQL server looks at the last value inserted into identity column and not at the last value that it has generated for the column. Pretty smart hunnn...