Reuse the Identity - 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

Reuse the Identity

Author

Topic

stpn
Starting Member

8 Posts

Posted - 2014-06-19 : 07:24:47

Hi all,

I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-19 : 08:15:29

Do you want to insert new records with the now empty values, or do you want to compress all the existing rows to have contiguous ids? See this article by Peter Larsson. http://www.sqlteam.com/article/efficiently-reuse-gaps-in-an-identity-column

stpn
Starting Member

8 Posts

Posted - 2014-06-19 : 08:48:38

thanks for your reply, i want to insert new record with the old deleted same id number

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-19 : 09:36:13

You would need to set IDENTITY_INSERT ON for the table, and then insert the values including the identity value. E.g.

SET IDENTITY_INSERT dbo.MyTable ON;
INSERT INTO dbo.MyTable (IdentityColumn, AnotherColumn, AThirdColumn) VALUES (244,'ABCD',57.5);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources