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:
<!--- Update the data records. --->
<cfquery name="qTurnOnInsert" datasource="#DATA.Source#" username="#DATA.Username#" password="#DATA.Password#">
<!--- Delete the data in the table. --->
DELETE FROM
[#strTableName#]
;
<!--- Turn on the ability insert the identity column. --->
SET IDENTITY_INSERT [#strTableName#] ON
;
</cfquery>
<!--- Loop over the data query to add new rows. --->
<cfloop query="qData">
<!--- Inser this row. --->
<cfquery name="qInsert" datasource="#DATA.Source#" username="#DATA.Username#" password="#DATA.Password#">
INSERT INTO [#strTableName#]
(
<cfloop index="intColumn" from="1" to="#ListLen(qData.ColumnList)#" step="1">
<cfif (intColumn GT 1)>,</cfif>
[#ListGetAt( qData.ColumnList, intColumn )#]
</cfloop>
) VALUES (
<cfloop index="intColumn" from="1" to="#ListLen(qData.ColumnList)#" step="1">
<cfif (intColumn GT 1)>,</cfif>
<cfqueryparam value="#qData['#ListGetAt( qData.ColumnList, intColumn )#'][qData.CurrentRow]#" cfsqltype="CF_SQL_VARCHAR" />
</cfloop>
)
;
</cfquery>
</cfloop>
<!--- Turn off the ability insert the identity column. --->
<cfquery name="qTurnOffInsert" datasource="#DATA.Source#" username="#DATA.Username#" password="#DATA.Password#">
SET IDENTITY_INSERT [#strTableName#] OFF;
</cfquery>
Want to use code from this post? Check out the license.
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
@Mike,
My pleasure.
"LET and SET statements are no longer supported" in ASP.NET
@Jim,
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()
@Jim,
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!
@Flip,
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
rad!
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?
Ben
Here is an ORM that works with SQL Server
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
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?
@Stephen - Make sure that the table with PK that the FK refers to is populated first. Also, you can only have one table at time with SET IDENTITY_INSERT set to 'ON', so you must set it to 'OFF' if you before setting it to 'ON' for the next table in a script. See the 'Remarks' section of this: http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx
HTH