INSERT INTO SQL Server table with IDENTITY column

Overview

In our dbo.Customer table outlined in this tutorial, the CustomerID column is an identity.  Here is how we explained that column earlier in this tutorial:

• For the CustomerID column, “IDENTITY(1,1)” is specified.  This means that as each row is inserted into the table, SQL Server will automatically increment this value by 1 starting with the number 1.
• This is a point of interest because as we begin to write our INSERT statements, we will need to make sure we do not include this column because SQL Server will manage it for us.

So what happens if we want to insert a record with a specific CustomerID?  For example, let’s say a customer was deleted by mistake and you want to retain their original CustomerID.  What would you do?  If you inserted the record like we have in the examples above, the next highest number would be inserted, not the original value.  Let’s check out the example below to show how can we resolve this issue. 

Explanation

Let’s explain the code below:

• Section 1 – Simple SELECT statement to retrieve all of the data from the dbo.Customer table.
• Section 2 – Delete a single record from the dbo.Customer table.
• Section 3 – Simple SELECT statement to retrieve all of the data from the dbo.Customer table.
• Section 4 – Simple INSERT statement that fails because an explicit value cannot be inserted into a table with an identity column when the IDENTITY_INSERT is set to OFF, which is the default setting.
  • Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table ‘Customer’ when IDENTITY_INSERT is set to OFF.
• Section 5 – Same simple insert statement as in the section above, with the addition of IDENTITY_INSERT logic to permit an explicit value to be inserted into the dbo.Customer table.
• Section 6 – Simple SELECT statement to retrieve all of the data from the dbo.Customer table to verify the INSERT command in Section 5 was successful.

-- 1 - Retrieve all of the data
-- from the dbo.Customer table
SELECT *
FROM dbo.Customer;
GO
-- 2 - Delete a single record
DELETE
FROM dbo.Customer
WHERE CustomerID = 1;
GO
-- 3 - Verify the record was deleted
SELECT *
FROM dbo.Customer;
GO
-- 4 - Insert the deleted record
-- Insert fails
INSERT INTO [dbo].[Customer]
([CustomerID]
,[FirstName]
,[LastName]
,[PhoneNumber]
,[EmailAddress]
,[Priority]
,[CreateDate])
VALUES
(1
,'Jonah'
,'Hook'
,'777-777-7777'
,'jonah@neverdull.com'
,1
,'2011-09-01');
GO
-- 5 - Insert the deleted record
-- Insert succeeds
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT INTO [dbo].[Customer]
([CustomerID]
,[FirstName]
,[LastName]
,[PhoneNumber]
,[EmailAddress]
,[Priority]
,[CreateDate])
VALUES
(1
,'Jonah'
,'Hook'
,'777-777-7777'
,'jonah@neverdull.com'
,1
,'2011-09-01');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
-- 6 - Verify the data
SELECT *
FROM dbo.Customer;
GO

Additional Information

For more information about the SET IDENTITY_INSERT command, check out these tips:

• Auto create identity insert SQL Server command to sync tables
• Using Identity Insert to keep SQL Server table keys in sync

Jeremy Kadlec

Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years.  He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.