For copying data from one to another table which is efficient,using INTO or using INSERT?

Question

There are two way to copy data from one table to another.

1. First create a new table then using "

INSERT INTO TABLE2_NAME 
SELECT * FROM TABEL1_NAME"

OR

2. SELECT * INTO TABLE2_NAME FROM TABLE1_NAME

I am using SQL Server 2012.

Answer 1

They are different inasmuch that the first one needs to have the table already created, the second one will create the table too.

I would pretty much bet that the execute plan for the read on TABLE1_NAME will be the same ergo the second is probably a few millisecs slower as it has to create the table.

Now the next thing to take into account is where the table is being created. In the first on you may have placed the table on fast storage while in the second the it will go to to the default primary storage which may or may not be optimised.

If all things are equal - then it's likely a micro optimisation that isn't worth considering.

Answer 2

The INSERT INTO TABLE2_NAME SELECT * FROM TABEL1_NAME creates TABLE2_NAME and inserts the values of TABEL1_NAME in them. It is more efficient but if the table is already created that statement would give an error.

The SELECT * INTO TABLE2_NAME FROM TABLE1_NAME only inserts the values of TABLE1_NAME in TABEL2_NAME.