Retain primary keys in event rows needs to be restored

Question

I have an SQL database with one-to-many and many-to-many relationships. Most of the relationships are based on an auto increment field "ID." In some cases when rows from tables are deleted, I export all the data from multiple tables to XML files before deleting the data from the database in the event that this data should need restoring at a later date.

How will the database IDs be treated? Can I simply restore the data into the existing database and the Ids and relationships will remain intact?

Any advice would be much appreciated.

Answer 1

You can re-insert the data, even into an IDENTITY field, provided you use SET IDENTITY_INSERT [table] ON

SET IDENTITY_INSERT yourTable ON

INSERT INTO
  yourTable (
    id,
    field1,
    field2
  )
SELECT
  id,
  field1,
  field2
FROM
  yourBackup

SET IDENTITY_INSERT yourTable OFF

Note that you must specify all of the fields, including the ID, in the INSERT statement.

Once you've re-created the records with the primary keys, then you can re-create the records in tables that use them as foreign keys.