Turning Off and On Identity Column in SQL Server
Posted April 21, 2006 at 2:05 PM
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:
Launch code in new window » Download code as text file »
- <!--- 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>
Download Code Snippet ZIP File
Comments (1) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
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.
Posted by another tech on Mar 27, 2007 at 2:29 PM
