CODEWITHSUNDEEP
sqlsql-servert-sqlselect
user3203331
user3203331

Reputation: 437

How can I copy a backup of a table into the main table?

I have TABLE1, before making changes I made a backup of the table: 

SELECT * INTO TABLE1BACKUP FROM TABLE1

I have made changes to the data in the backup of the table, so now I want to copy the backup table data into the main table. How can I get back my original data? I need to truncate my main table and copy all the data from backup table.

Upvotes: 3

Views: 1626

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

You can only SELECT INTO a new table. In your case, you need:

TRUNCATE TABLE dbo.Table1;
INSERT dbo.Table1 SELECT * FROM dbo.Table1Backup;

Or other options (e.g. the above won't work if there are foreign keys):

DELETE dbo.Table1;
INSERT dbo.Table1 SELECT * FROM dbo.Table1Backup;

If there are foreign keys and child rows that point to this table, you'll need to drop or disable those constraints first too.

If there are no constraints etc. that you need to worry about, an even less intrusive way to do this is:

BEGIN TRANSACTION;
EXEC sp_rename 'dbo.Table1', N'Table1Old', OBJECT;
EXEC sp_rename 'dbo.Table1Backup', N'Table1', OBJECT;
COMMIT TRANSACTION;
DROP TABLE dbo.Table1Old;

Upvotes: 5

Related Questions