Turning Off and On Identity Column in SQL Server
I have been moving over the data from one database to another and am currently using XML files as an intermediate step. The process has been going very smoothly, and I just learned something new. I need to keep certain ID column values across the database and was worried about turning on and off identity column AND automating the XML to DB scripting. But, found a great solutions.
SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF
It’s wicked cool. But, it actually took me a bit to debug. I had the ON/OFF confused. I thought they were referring to the DB’s insert functionality, and so, I thought I had to turn it OFF in order to insert my own values. I realize now, that it is referring to the SQL statements ability to insert identity column value, and hence, I need to turn it ON to run my scripts.
It all comes together rather nicely:
Want to use code from this post? Check out the license.
Enjoyed This Post? ❤️ Share the Love With Your Friends! ❤️
Reader Comments
MS-SQL DTS would work a lot better for data transfer. You can carry the indexes and other objects if necessary. Also alows pulling data by query and from just about any data source.
Thanks for the code, this makes my job of inserting Identity values into another table that much easier.
Best,
Mike D
«LET and SET statements are no longer supported» in ASP.NET
What do you mean in ASP.NET? The «SET» command here is part of SQL. Are you referring to a specific database?
Sorry, it works perfectly well, I just didn’t execute it as a SQL statement.
cmd1.commandtext = «SET IDENTITY_INSERT dua ON»
cmd1.ExecuteNonQuery()
Ah, no worries 🙂 Glad you got it working now.
I’m still getting an insert error when I have turned the identity on.
(SET IDENTITY_INSERT dao.Applications ON)-> Command(s) completed successfully.
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘Applications’ when IDENTITY_INSERT is set to OFF.
I added «GO» before and after my insert stmnts and it worked! Thanks!
Interesting. What database system are you using?
I have a remark (maybe trivial, maybe not) — I tried executing this via Management Studio and after this doing insert in another query editor and it kept failing. But when I pasted both into one window and executed in a single click then it worked fine — seems like the setting is rather temporary.
How do I deal with turning Identity_Insert On with ORM? All I’m doing is creating a new entity and then calling the entitySave() method, and I get the message: «Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.»
Maybe I could insert a cfquery and explicitly turn it on before calling the entitySave method, but wouldn’t that defeat the purpose of the ORM framework?
I just keep getting
«Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.»
I am using SQL Server 2008 and executing the the script from SQL Server Management Studio
Thanks dude, i am confused between
(SET IDENTITY _INSERT tablename ON) or (SET IDENTITY_INSERT tablename ON) thanks.
how to call database function in SeaSar2
USE AdventureWorks2008R2;
GO
— Create tool table.
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
— Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES (‘Screwdriver’)
INSERT INTO dbo.Tool(Name) VALUES (‘Hammer’)
INSERT INTO dbo.Tool(Name) VALUES (‘Saw’)
INSERT INTO dbo.Tool(Name) VALUES (‘Shovel’)
GO
— Create a gap in the identity values.
DELETE dbo.Tool
WHERE Name = ‘Saw’
GO
SELECT *
FROM dbo.Tool
GO
— Try to insert an explicit ID value of 3;
— should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’)
GO
— SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
— Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’)
GO
SELECT *
FROM dbo.Tool
GO
— Drop products table.
DROP TABLE dbo.Tool
GO
I have used the SET IDENTITY_INSERT feature several times when migrating data, but have always done so in an «off-line» mode. Can this feature be used while the database is online taking other requests? In other words, will the setting of IDENTITY_INSERT to ON affect only my specific session or is will it affect every insert into that table?
you are awesome..i am lucky to get this blog between such a garbage one. Thanks, Prashant
I’ve been using this technique to transfer data from our PROD databases to our DEV databases because I really need to keep the IDs in synch. But it doesn’t work when a table contains Foreign Keys. Is there some special command required for Foreign Key Inserts?
Remove Identity from a column in a table
We have a 5GB table (nearly 500 million rows) and we want to remove the identity property on one of the column, but when we try to do this through SSMS — it times out.
Can this be done through T-SQL?
11 Answers 11
You cannot remove an IDENTITY specification once set.
To remove the entire column:
Information about ALTER TABLE here
If you need to keep the data, but remove the IDENTITY column, you will need to:
- Create a new column
- Transfer the data from the existing IDENTITY column to the new column
- Drop the existing IDENTITY column.
- Rename the new column to the original column name
If you want to do this without adding and populating a new column, without reordering the columns, and with almost no downtime because no data is changing on the table, let’s do some magic with partitioning functionality (but since no partitions are used you don’t need Enterprise edition):
Как отключить identity ms sql
HI all whoever are searching for this problem
Allows explicit values to be inserted into the identity column of a table.
Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] < table >
Arguments
Is the name of the database in which the specified table resides.
Is the name of the table owner.
Is the name of a table with an identity column.
Remarks
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
HI all whoever are searching for this problem
Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] < table > < ON | OFF >Allows explicit values to be inserted into the identity column of a table.
I think you have miss read the thread. The command which you are mentioning is to switchoff identity property. THe question was how to drop identity property of a column probably using SMO. Even the earlier post (which recommend system table update will not work in 2005 and its not recommened either in 2000.
I found one way only — recreate table and copy old data to a new table or recreate column. Any way, if you have billion rows in your table, it will be like the ____.
I know it has been some time after you posted you question but still.
***********************
sp_configure ‘allow update’, 1
go
reconfigure with override
goupdate syscolumns set colstat = 0 —turn off bit 1 which indicates identity column
where and name = ‘column_name’
goexec sp_configure ‘allow update’, 0
go
reconfigure with override
go***********************
Julia
This solution works fine for me.
Simple, fast and didn’t change the timestamp if you have a column of this kind (that’s what I was looking for. )
Well i think the answer is provided couple of times above; you have two ways either create new table or Add one new column copy the data into that column and drop the identity column;
the soulution provided by Kevin above is not suitable for 2005 and above version as Microsoft dont allow to uupdate in the system tables.
Hi,
I have one ETL Package where I roll back data from Destination to Source. To do so, I disable all constrains on tables by using ALTER TABLE "Table_Name" NOCHECK CONSTRAINT ALL on begenning and at end I enable all constrains on tables by using ALTER TABLE "Table_Name" CHECK CONSTRAINT ALL.
For few tables, I have IDENTITY column. My question, when I use ALTER TABLE "Table_Name" NOCHECK CONSTRAINT ALL, do it disable IDENTITY column Constraint also?
Please clarify it.
Thanks Shiven:) If Answer is Helpful, Please Vote
If in OLE DB Destination Editor (Connection Manager Page)
Data access mode = Table or view – fast load Or Table name or view name variable – fast load,
If you check option " Keep identity" then you can map a IDENTITY column. Keep identity: Specify whether to copy identity values when data is loaded. This property is available only with the fast load Or Table name or view name variable – fast load option.
The default value of this property is false (Unchecked) which means you can not map IDENTITY Column .
In your ETL Package If you have checked option " Keep identity" and once load completed then you want to enable IDENTITY Column properties then you need to execute "SET IDENTITY_INSERT Schema_Name.Table_Name OFF".
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.
Thanks Shiven:) If Answer is Helpful, Please Vote
- Proposed as answer by Shivendoo Kumar Dubey Monday, October 10, 2011 6:44 AM
- Edited by Shivendoo Kumar Dubey Monday, October 10, 2011 6:46 AM
— Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
— Inserting values into products table.
INSERT INTO products (product) VALUES (‘screwdriver’)
INSERT INTO products (product) VALUES (‘hammer’)
INSERT INTO products (product) VALUES (‘saw’)
INSERT INTO products (product) VALUES (‘shovel’)
— SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
— Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, ‘garden shovel’).
SET IDENTITY_INSERT [ database. [ owner. ] ] < table > ON = can insert an explicit ID value
SET IDENTITY_INSERT [ database. [ owner. ] ] < table > OFF = can not insert an explicit ID value
Thanks Shiven:) If Answer is Helpful, Please Vote
- Edited by Shivendoo Kumar Dubey Monday, October 10, 2011 6:57 AM
No, This doesn’t disable the IDENTITY on the column.
You have to explicitly turn it ON/OFF
It’s kind of an old post, but I’m hoping is still relevant. I’m trying to do the same on a SQL 2008 server and getting
Ad hoc updates to system catalogs are not allowed.
am I missing something?
- Proposed as answer by donna_123_a Friday, May 10, 2013 8:47 PM
- Unproposed as answer by donna_123_a Friday, May 10, 2013 8:47 PM
Looks like you have to drop the identity property via SSMS manually. However, while doing this in SSMS, you can script out the SMO commands so that you can repeat it again later with just the SQL. which will basically drop and recreate the table. Here’s the post for that:
here is the quick shortcut to remove the identity property
Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
Tried this.
sp_configure ‘allow update’, 1
go
reconfigure with override
go
update syscolumns set colstat = 0 —turn off bit 1 which indicates identity column
where and name = ‘ProviderMarkets_key’
go
exec sp_configure ‘allow update’, 0
go
reconfigure with override
go
got this error msg.
Configuration option ‘allow updates’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option ‘allow updates’ changed from 1 to 0. Run the RECONFIGURE statement to install.
/****/
basically tried a simple test table with id as int with identity constraint.
inserted 1 value into it.
Then tried this sp to delete and got same error.
Tried via clicking way and got a msg suggesting that I have to drop and recreate the table to remove id constraint.
. I was basically trying this on a table that has 13M records; due to identity being changed to sequence constraint due to requirement change in my project.
only way I did it was drop the table and re-create with sequence implementation.. and re-run the job via SSIS/job scheduler(s) way.
Thanks,
Rai.
How do I copy a table with SELECT INTO but ignore the IDENTITY property?
results in copy_from_with_id_1 with identity on id too.
The following stack overflow question mentions listing all columns explicitly.
Oops, even in this case id is an identity column.
What I want is a table like
10 Answers 10
The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in «Working with Identity Columns» in the Remarks section.
- The SELECT statement contains a join, GROUP BY clause, or aggregate function.
- Multiple SELECT statements are joined by using UNION.
- The identity column is listed more than one time in the select list.
- The identity column is part of an expression.
- The identity column is from a remote data source.
So. you could theoretically get away with:
It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.
Inspired by Erics answer, I found the following solution which only depends on the table names and doesn’t use any specific column name :
Edit
It is even possible to improve this to
You can use a join to create and populate the new table in one go:
Because of the 1 = 0 condition, the right side will have no matches and thus prevent duplication of the left side rows, and because this is an outer join, the left side rows will not be eliminated either. Finally, because this is a join, the IDENTITY property is eliminated.
Selecting just the left side columns, therefore, will produce an exact copy of dbo.TableWithIdentity data-wise only, i.e. with the IDENTITY property stripped off.
All that being said, Max Vernon has raised a valid point in a comment that is worth keeping in mind. If you look at the execution plan of the above query:
you will notice that the source table is mentioned in the execution plan just once. The other instance has been eliminated by the optimiser.
So, if the optimiser can correctly establish that the right side of the join is not needed in the plan, it should be reasonable to expect that in a future version of SQL Server it may be able to figure out that the IDENTITY property need not be removed either, since there is no longer another IDENTITY column in the source row set according to the query plan. That means that the above query might stop working as expected at some point.
But, as correctly noted by ypercubeᵀᴹ, so far the manual has explicitly been stating that if there is a join, the IDENTITY property is not preserved:
When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless [. ] [t]he SELECT statement contains a join.
So, as long as the manual keeps mentioning it, we can probably rest assured that the behaviour will stay the same.
Kudos to Shaneis and ypercubeᵀᴹ for bringing up a related topic in chat.