Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Chris Peters
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Chris Peters@cf_chrispeters )

Turning Off and On Identity Column in SQL Server

By on
Tags: ,

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

1 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.

1 Comments

Thanks for the code, this makes my job of inserting Identity values into another table that much easier.
Best,
Mike D

2 Comments

Sorry, it works perfectly well, I just didn't execute it as a SQL statement.

cmd1.commandtext = "SET IDENTITY_INSERT dua ON"
cmd1.ExecuteNonQuery()

2 Comments

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.

1 Comments

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.

1 Comments

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?

1 Comments

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

1 Comments

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

1 Comments

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?

1 Comments

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?

1 Comments

@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