INSERT INTO SQL Server table with IDENTITY column

By: Jeremy Kadlec

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'
 ,'[email protected]'
 ,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'
 ,'[email protected]'
 ,1
 ,'2011-09-01');
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO

-- 6 - Verify the data
SELECT * 
FROM dbo.Customer;
GO

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