views:

201

answers:

3

How do I restore my data from a backup table table1_bu into a new table new_table1, which has the same structure? I need to insert all the rows from table1_bu into new_table1.

+2  A: 

Use this:

select * into new_table1 from table1_bu

Note that for this to work, new_table should not exist before running the statement, this will create AND populate the table.

ck
+5  A: 

Hi Alex

INSERT INTO new_table1(Id, Field1)
  SELECT Id, Field1
    FROM table1_bu

Greets, Matthias

Mudu
This will not work if the Id column is set to be an identity column
Thorarin
+4  A: 

Assuming you want to use the same IDs in the new table:

SET IDENTITY_INSERT new_table1 ON;

INSERT INTO new_table1
SELECT * FROM table1_bu;

SET IDENTITY_INSERT new_table1 OFF;

PS: SELECT INTO (as suggested by some) also works, but it's slightly less flexible in my experience. Therefore I've gotten used to this way of doing things :)

Thorarin