SQL Server 'select * into' versus 'in

Question

Say table1 and table2 already exist, is there any difference between these queries

query1 :-

select * into table1 from table2 where 1=1

query2: -

insert into table1 select * from table2

Answer 1

The select * into table1 from table2 where 1=1 creates table1 and inserts the values of table2 in them. So, if the table is already created that statement would give an error.

The insert into table1 select * from table2 only inserts the values of table2 in table1.

Answer 2

In query2, the table table1 must exist before running the command

In query1, table1 will be created or an error will be thrown if it already exists

Answer 3

The first one (SELECT INTO) will create and populate a new table the second (INSERT... SELECT) inserts to an existing table.

In versions of SQL Server prior to 2008 the first one could be minimally logged and the second one not but this is no longer true.

Answer 4

INSERT INTO TABLE_A SELECT * FROM TABLE_B

Is a commonly used sentence, which is used to insert values of a table into another table. Selected columns can also be inserted using this.

SELECT * INTO TABLE_A FROM TABLE_B

Will create a new TABLE_A populated with values of TABLE_B

Answer 5

select * into table1 from table2 where 1=1

The query above requires that the table DOES NOT exist. You do not need to specify columns as all columns are created as they are retrieved from the source table.

insert into table1 select * from table2 

For the above query, you need an EXISTING table1. The columns in both tables should also be in exactly the same order, otherwise you need to provide a column list for both tables.