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.

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

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.