Restore a table from another table

Question

SQL Server 2000

I backup a table like below:

select * into bkp_table from src_table;

How can I restore from backup table to source table? Truncate the src_table? Thanks.

Answer 1

Yes

truncate table src_table

insert src_table
select * from bkp_table 

now if you have an identity column, you need to do SET IDENTITY_INSERT src_table ON after the truncate (the truncate will reset it to 0 BTW)

Then do you insert and then SET IDENTITY_INSERT src_table OFF

Answer 2

1. Truncate the table

2. Insert back records in to the table, keeping the same Identity as in Backup

   SET IDENTITY_INSERT src_table ON

   INSERT src_table(TheIdentity, TheValue) SELECT * FROM bkp_table

   SET IDENTITY_INSERT src_table OFF