Anonymous Creator
Anonymous Creator

Reputation: 3819

Delete and Recycle Auto incremented value in SQL

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

Answers (1)

Dan Guzman
Dan Guzman

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

Related Questions