Inserting multiple rows in SQL

Question

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.

Answer 1

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.

Answer 2

Hi Alex

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

Greets, Matthias

Answer 3

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 :)