Put back deleted row from another db while preserving id field

Question

Hi,

I have SQL 2005 databases.

I have deleted a row from one of them and want to get it back from another database that was a backup of the row.

How do isnert it while preserving its id primary key identity field?

Can you give TSQL to do this assume databases are called "tbrPdata" and "tbr0910" which is the backup?

Malcolm

Answer 1

Use SET IDENTITY_INSERT:

SET IDENTITY_INSERT tbrPdata.dbo.TABLE ON
GO

 INSERT INTO tbrPdata.dbo.TABLE
   (col1, col2, col3,...)
 SELECT t.col1, t.col2, t.col3,...
   FROM tbr0910.dbo.TABLE t
  WHERE t.id = ?

SET IDENTITY_INSERT tbrPdata.dbo.TABLE OFF
GO