Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to insert deleted keys in a table where there is an identity(1,1) column in a table, without disabling identity constraint?

user-image
Question added by Abdul Rafi K , Technical Lead , Symphony Teleca
Date Posted: 2016/02/28
Mosam Vadiekar
by Mosam Vadiekar , Senior dot net developer , Remote Solutions System Limited

If we have TableA with ideantity(1,1) -Generate a new script for TableA without identity(1,1) to a column

-Rename the table name to TableB . Run the script 

-Insert the values from TableA to TableB. (Now the data in the TableA and TableB are same)

-Insert the deleted keys in TableB. (So we now have deleted keys)

-Go to TableB properties and set Identity(1,1) to the column

-Drop table TableA-Rename TableB to TableASo we successfully inserted deleted keys where identity(1,1) in a column without disabling identity contraint.

baiju mohanan
by baiju mohanan , BI Developer , OSN

set  IDENTITY_INSERT ON  for insert the records into the table 

Aşkın Burak Duran
by Aşkın Burak Duran , Software Team Leader , Intertech A.Ş

set IDENTITY_INSERT ON before insert operation.

Deleted user
by Deleted user

SET IDENTITY_INSERT to ON before issuing the INSERT INTO command.

Premkumar P
by Premkumar P , Project Associate - Technical , Mindloficx Infratec Ltd.,

Using Reseed command and Insert the records

Gary Odendaal
by Gary Odendaal , SQL Report Writer , VAT I.T

Tried to think of how i would do this but yeah, Mosam Vadiekar summed it up perfectly. 

prince mathooru house
by prince mathooru house , Junior Software Developer , Iware Solutions

we can create a triger for the table. if we delete the table data it will keep in the backup table. so we can restore the lost data any time. it is a simple solution but we have many other ways to restore the deleted datas.

Abdul Rafi K
by Abdul Rafi K , Technical Lead , Symphony Teleca

The answer is to use RESEED keyword and insert the missing record

More Questions Like This