Friday, 9 May 2008

Resetting an Identity Field to 0 when testing a data migration process

I currently have a SSIS package that imports data from Excel by flushing data out of the existing SQL tables and grabbing the new values from the source spreadsheet. I noticed that while testing these scripts, my identity seed value was ominously creeping up. When I moved this to live after testing the migration, this would mean the identity values would be artificially large.

Never fear - It IS possible to reset the identity seed value by using the "DBCC CHECKIDENT" command. For example, to reset a table so that the next identity values is 1, you can run the following as part of your data migration script:


This will force the next value generated to be 1. Also note that the DBCC CHECKIDENT command even works with fully qualified names for the tablename parameter. For example:

ALTER PROCEDURE [dbo].[proc_InitializePrimaryTables]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
TRUNCATE TABLE InvestmentManagementDataMigration..OpportunityStatusHistory
DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunityStatusHistory', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..OpportunityUserRole
DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunityUserRole', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..FundInterest
DBCC CHECKIDENT('InvestmentManagementDataMigration..FundInterest', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..OpportunitySourceContact
DBCC CHECKIDENT('InvestmentManagementDataMigration..OpportunitySourceContact', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..AssetOwnership
DBCC CHECKIDENT('InvestmentManagementDataMigration..AssetOwnership', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..AssetRelationship
DBCC CHECKIDENT('InvestmentManagementDataMigration..AssetRelationship', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..Opportunity
DBCC CHECKIDENT('InvestmentManagementDataMigration..Opportunity', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..Contact
DBCC CHECKIDENT('InvestmentManagementDataMigration..Contact', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..Company
DBCC CHECKIDENT('InvestmentManagementDataMigration..Company', RESEED, 0)
DELETE FROM InvestmentManagementDataMigration..Address
DBCC CHECKIDENT('InvestmentManagementDataMigration..Address', RESEED, 0)


Just to be tricky, I tried setting the seed to a negative value (-1) just to see what this would do...and the next value generated was 10. I don't know about you, but last time I checked, 10 does not follow -1!

1 comment:

David Klein said...

Side Note:

To make sure your identity values aren't out of sync, you can run the scripts generated by the following statement. It will update identities in case the next generated identity value would conflict with freshly imported data:

SELECT 'DBCC CHECKIDENT (''' + TABLE_NAME + ''') ' FROM information_schema.tables