Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

INSERT INTO SQL Server table with IDENTITY column



By:

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:


Last Update: 9/16/2011




More SQL Server Solutions











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.



    



Learn more about SQL Server tools