Identity column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity column

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-01-11 : 10:04:36

I exported a table to a different database. Added some records and now I want to export the table back to the orginal database. When I do this, I get an error "Cannot insert Duplicate Key" The problem seems to be with the identity column.

I am choosing to delete records when exporting the table back.

Is there a script that can be run to reset the ID column?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-11 : 10:11:51

Just so you understand is it because of a unique constraint (ie: a primary key) that is causing the error. An identity column doesn't care if the value already exists. You can reseed the identity value to a number greater than the max id with:


DBCC checkident ('<tableName>', RESEED, <newValue>)

Another option is to explicitly add whatever value you want by setting:


Set identity_insert <TableName> ON
insert your new values
Set identity_insert <TableName> OFF

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources