Reputation: 3819
In one of my database,
One table lets say "Active Records" has one column which has auto incremented value or say identity column.
Now records from that table has been removed to lets say "Deleted Records" table.
Now. I want it back to the original table "Active Records".
Is this possible to move to original table with that deleted records with its original identity?
As I want to maintain other relationships (which I have not deleted yet) with that deleted records. (This is because user might have deleted records mistakenly. So I want to allow them to recycle records)
Is this possible in any way?
Example.
I insert records in "Active Records". so auto increment value will be 1,2,3,4 and so on.
Now I delete record 2 and move that record to "Deleted record"
Now user wants it back. But if I insert that record to "Active Records" it will be 5 valued because of auto increment. But my value should retain which is 2.
Upvotes: 0
Views: 215
Reputation: 46231
You can specify IDENTITY_INSERT...ON
so that the provided values are used instead of an auto-assigned value. An explicit column list must be specified, which includes the IDENTITY
column. For example:
SET IDENTITY_INSERT dbo.ActiveRecords ON;
INSERT INTO dbo.ActiveRecords(RecordID, OtherData)
SELECT RecordID, OtherData
FROM dbo.DeletedRecords
WHERE RecordID = 2;
SET IDENTITY_INSERT dbo.ActiveRecords OFF;
Upvotes: 1