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
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
If You Think Flattery Will Get You Anywhere...
Older Post
ColdFusion Runtime Abort Exception
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.
