What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0

QuickTip: Working with T-SQL Identities

I recently had to write a data migration script from one SQL Server DB to another one.  I could have used SSIS, but it would have required a learning curve that our timeframe didn't permit.  A few hours later I had a functioning script that moved over most of the useful data (2 days later I discovered some more data that had to be migrated, but that's a separate issue).


In developing the migration script I had to deal with a series of key pool tables, each with their own identity columns for key generation.  My script needed to manipulate these tables and identity values which meant twittering the identity related triggers.


Here's 3 quick commands I discovered useful:

To Disable Identity Key Value Generation

When the identity generation is disabled, you must manually insert key values for the identity column.  Note:  Sql will not ensure your manually entered values are unique.  This can be a pro or con depending on your situation.

                            SET IDENTITY_INSERT [TableName] ON

It seems counter-intuitive to me to disable the identity trigger by turning something ON, but there you go.

To Restore Identity Key Value Generation

                           SET IDENTITY_INSERT [TableName] OFF

To Reset the Seed Value of an Identity Column

                           dbcc checkident ('[TableName]' , reseed, NewSeedValue)     

Print | posted on Saturday, March 8, 2008 8:48 PM | Filed Under [ SQL ]


No comments posted yet.
Post A Comment

Powered by: