Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Insert missing SQL Server IDENTITY column values using SET IDENTITY_INSERT command


By:   |   Last Updated: 2018-09-26   |   Comments (6)   |   Related Tips: More > Identities

Problem

While designing a SQL Server database, the primary key column is often set to auto-increment. To do this, the IDENTITY constraint is set on the primary key column. The starting position and the increment step are passed as parameters to the IDENTITY column. Then whenever a new record is inserted, the value of the IDENTITY column is incremented by the pre-defined step, usually a number. Now if a record is deleted, the IDENTITY column value for that record is also deleted. If a new record is inserted, its value for the IDENTITY column will be incremented from the previous figure in the column. It is not possible to reuse the value that was originally used by the now deleted record. If you try to specify the value for the IDENTITY column, an error will be thrown. So how do you reuse the value that was assigned to the deleted record?

Solution

The solution to this problem is to switch on the SET IDENTITY INSERT flag, which is off by default. Switching the SET IDENTITY INSERT flag to ON allows for the insertion of any random value to the IDENTITY column, as long as it doesn't already exist.

In this article, I will explain (with the help of an example) how to insert a missing value into the IDENTITY column.

Setup Example Table and Data

First let’s create some dummy data. We will execute our sample queries on this new database.

CREATE DATABASE School
GO

USE School
GO

CREATE TABLE Students
(
   Id INT PRIMARY KEY IDENTITY(2,2),
   StudentName VARCHAR (50),
   StudentAge INT
)
GO

INSERT INTO Students VALUES ('Sally', 25 )
INSERT INTO Students VALUES ('Edward', 32 )
INSERT INTO Students VALUES ('Jon', 24 )
INSERT INTO Students VALUES ('Scot', 21)
INSERT INTO Students VALUES ('Ben', 33 )

In the script, we create a dummy database “School”. Next, we execute the script that creates a table named “Students”. If you look at the table design, you can see that it contains three columns Id, StudentName and StudentAge. The Id column is the primary key column with an IDENTITY constraint. Both the seed and step values for IDENTITY are set to 2. This means that the first record in the “Students” database will have the Id value of 2 and each subsequent record will have a value incremented by 2. Finally, we insert 5 random records into the Students table.

Now if you select all the records from the Students table, you will see that Id column will contain a sequence of values starting at 2 and incremented by 2 in each row. Execute the following script:

SELECT * FROM Students

The output looks like this:

result set

You can see that the first record has an id value of 2 while the 5th has a value of 10.

Now, suppose one of the students leaves the school and we want to delete his record. We can do so using a simple DELETE statement. Let’s delete the record of Jon:

DELETE FROM Students WHERE StudentName = 'Jon'

Now if you again look at all the records in the Students table using SELECT statement, you will see the following output:

SELECT * FROM Students
result set

You can see from the output that record of the student “Jon” with Id 6 has been deleted.

Now let’s try to insert a record of a new student and see what Id it gets:

INSERT INTO Students VALUES ('Jessica', 27 )

The above script inserts a record of a new student named “Jessica”, aged 27, to the Students table.

To see the Id assigned to the Jessica, again retrieve all records from the Students table using the SELECT statement as shown below:

SELECT * FROM Students

The output looks like this:

result set

You can see from the output that Jessica has been assigned the Id 12 instead of the Id 6 vacated by Jon. The reason for this behavior is the fact that by default IDENTITY column assigns a value to a new record by adding the step to previous maximum value in the column instead of filling the vacant values in the column. Since the previous maximum value in the Id column was 10, therefore Jessica is assigned 12, since the step is 2.

Depending upon the business rules of the application being developed, this behavior can be correct. For instance, a School may have a rule that even if a student leaves the school, his/her Id cannot be assigned to a new student. On the other hand, there can be a school that reassigns the Id of a student who leaves the school, to a new student.

Manually Insert Record with specific ID value

In the latter case, one of the solutions is to manually insert the Id value for the new student.

Let’s try to add a record of a new student and manually set the value for the Id column to 6 as shown below:

INSERT INTO Students VALUES (6, 'Nick', 22 )

The above script inserts a record of a new student named “Nick”, aged 22, and Id 6, to the students table. When you try to execute the above script, an error will be thrown which looks likes this:

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

In simple words, the error says that since the flag IDENTITY_INSERT is off for the Id column, we cannot manually insert any values. Another important consideration is that we need to specify the column names as well while inserting data to IDENTITY column.

If we try to just specify the column names as mentioned in the above error message as follows:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

We get this error message.

Msg 544, Level 16, State 1, Line 35
Cannot insert explicit value for identity column in table 'Students' when IDENTITY_INSERT is set to OFF.

To manually insert a new value into the Id column, we first must set the IDENTITY_INSERT flag ON as follows:

SET IDENTITY_INSERT Students ON;

To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table.

Now if we again try to insert the record of the student “Nick” with Id 6, no error will be thrown. Execute the following statement again:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

You can see that we have specified the name of the columns as well for inserting a record.

Now again use SELECT statement to retrieve all records from students table in order to view if our new record has been inserted or not. The SELECT statement will return the following records.

SELECT * FROM Students
result set

You can see from the output that record of a new student named “Nick”, aged 22, and Id 6 has been inserted to the Students table.

Try Inserting Duplicate Values

If we try to insert duplicate values as follows:

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

We will get this error, since ID is the primary key for the table which has to be a unique value.

Msg 2627, Level 14, State 1, Line 35
Violation of PRIMARY KEY constraint 'PK__Students__3214EC071AD8EC3F'. Cannot insert duplicate key in object 'dbo.Students'. The duplicate key value is (6).
The statement has been terminated.

Note, that if the ID column was not a Primary Key we would be able to insert duplicate records.

Turn IDENTITY INSERT off

When you SET INDENTITY_INSERT ON it will stay on for the entire session (the time the query window is open).  So once this is set you can insert as many records as you want.  Also, this only applies for the session where this is turned on, so if you open another query window you would need to set this ON for that query window.

To turn off this option for the session, you would issue the following statement.

SET IDENTITY_INSERT Students OFF;
Next Steps


Last Updated: 2018-09-26


get scripts

next tip button



About the author
MSSQLTips author Ben Richardson Ben is the owner of Acuity Training, a UK based IT training business offering SQL training up to advanced administration courses.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 26, 2018 - 6:38:13 AM - sheetal chavanke Back To Top

Very helpful sir.


Thursday, October 04, 2018 - 5:58:40 PM - Ronald Rex Back To Top

Normally you would implement a cascade update delete when deleting parent records. But if you deleted child records this should not have an impact because the records would be referenced by the foreign key. But I was wondering in regards to if I was developing a clint facing application could i retrieve keys that have been deleted and reuse them when the user is entering a new record. I dont see how this would present a problem since we can use a unique constraing as opposed to a primary key to uniquely identify the enity as opposed to the row. 

 


Thursday, October 04, 2018 - 8:44:07 AM - Roger Plowman Back To Top

While the article is perfectly accurate the intent behind it needs careful scrutiny. There are a lot of reasons you may not want to reuse identities.

First, identities are normally used as primary keys, and primary keys should (at least IMO) be forever unique. That means if you delete the record with a PK of 6 then 6 is gone forever, never to be reused. That way any logs talking about that record can be retained for the lifetime of the application--and not be confused with a different record 6 (i.e. a newly added one that took advantage of reusing identities).

Having said that, when replacing an application (i.e. a full redesign) you absolutely should take advantage of SET_IDENTITY_INSERT when copying the data to a new schema to close up unused records. This is harder than you might think and requires a special transfer application to map old keys to new ones, but is usally worth the effort.

But I wouldn't recommend reusing identities as part of the application design.


Thursday, October 04, 2018 - 4:07:51 AM - Tony Milton Back To Top

This would be extremely dangerous if referential integrity is not set up (as happens in quite a few databases I've come across), i.e. inserting a new record with a previously existing Id value. If all child records were not deleted along with the parent, then the new record would link to them creating a severely incorrect set of data.

I'd always allow for the gaps in identity values and let the server deal with increments - be very wary of interfering with primary key value allocation.

Tony


Wednesday, September 26, 2018 - 10:26:43 AM - Greg Robidoux Back To Top

Thanks Mark.  This has been updated.

-Greg


Wednesday, September 26, 2018 - 10:11:08 AM - Mark Back To Top

 "You can see from the output that record of the student “Jon” with Id 2 has been deleted. "

That ID was actually 6, not 2 :)


Learn more about SQL Server tools