About the “set identity_insert” Command in SQL Server
The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table. Now, someone might wonder, “why on Earth would somebody want to do that?!”
Oh well, you know, there is always a good reason! 🙂 I can give you some reasons below:
- Inserting portions of data from the original database to a schema-only copy copy of the same database and you want to maintain the same values for the table’s identity column.
- Performing a “data-rescue” operation, that is you are trying to fix the data in a corrupted table.
Let’s See an Example
So, as always, an explanation is not enough. you want examples, I want examples, everybody wants examples to fully understand something. Therefore, consider the below simple example.
Let’s say we have the below temporary table named “#myTable“:
CREATE TABLE #myTable ( id INT PRIMARY KEY IDENTITY(1, 1) , code VARCHAR(50) , descr VARCHAR(150) ); GO
As you can see, our table’s primary key is the column “id” which is an identity column which started with value 1 and with each new insert is increased by 1.
Now, for some reason, let’s consider that the there was “some problem” during certain operations and the table ended up having the below data:
As you can see, based on the table’s data logic, there is a record missing, that is the table should contain a record with id=3, code=’code3′ and descr=’descr3′.
Within the context of our example, you are the Database Developer (or DBA in some cases where there is not a segregation of duties, which is wrong) and you are asked to fix this data-related problem by inserting into the table the missing record.
Reproducing the error message
If you are a little bit inexperienced, the first thing you would try, would be to directly insert the missing record. However, since “id” is an identity column, this action would result to an error message as shown below:
As you can see in the screenshot, you get the error message: Cannot insert explicit value for identity column in table ‘#myTable’ when IDENTITY_INSERT is set to OFF.
Using the “set identity_insert” command for resolving the issue
So, here’s where the use of the “set identity_insert” comes in handy. So, you can consider modifying this example’s code as below:
SET IDENTITY_INSERT #myTable ON; GO INSERT INTO #myTable ( id , code , descr ) VALUES ( 3, 'code3', 'descr3' ); GO SET IDENTITY_INSERT #myTable OFF; GO
After running the above piece of code, as you can see below, you were finally allowed to insert the record with explicitly defining the identity column’s value (in this case id=3):
You need to be careful with identity_insert and use it with caution, because if you make misuse of this command there is always the risk to create data quality issues.
Learn more tips like this! Check our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Did you find this article useful and interesting? Find hundreds of useful SQL Server programming/development articles in my eBook: “Developing with SQL Server (Second Edition“.
Check our other related SQL Server Development articles.
Check out our latest software releases!
Subscribe to our newsletter and stay up to date!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Essential SQL Server Development Tips for SQL Developers (New)
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Complete Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Check our Related SQL Server Development Articles:
- Essential SQL Server Development Tips for SQL Developers
- How to Import and Export Data in SQL Server
- Listing all Tables of a Linked Server’s Database
- How to Import and Export Unstructured Data in SQL Server – FileTables
- How to Import and Export Unstructured Data in SQL Server – FILESTREAM
- How to Import and Export Unstructured Data in SQL Server – The IMAGE Datatype
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).