Communiquez avec les autres et partagez vos connaissances professionnelles

Inscrivez-vous ou connectez-vous pour rejoindre votre communauté professionnelle.

Suivre

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 ajoutée par Abdul Rafi K , Technical Lead , Symphony Teleca
Date de publication: 2016/02/28
Mosam Vadiekar
par 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
par baiju mohanan , BI Developer , OSN

set  IDENTITY_INSERT ON  for insert the records into the table 

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

set IDENTITY_INSERT ON before insert operation.

Utilisateur supprimé
par Utilisateur supprimé

SET IDENTITY_INSERT to ON before issuing the INSERT INTO command.

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

Using Reseed command and Insert the records

Gary Odendaal
par 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
par 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
par Abdul Rafi K , Technical Lead , Symphony Teleca

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

More Questions Like This