Difference between INSERT INTO and SELECT INTO
INSERT INTO and SELECT INTO are different because they do different things. Three main differences are:
- INSERT INTO is used when the table exists. SELECT INTO works when table does not exists. In other words INSERT INTO is used to insert data in existing table which may or may not have data. SELECT INTO creates new table according to received data.
INSERT INTO with no table hints is normally logged. SELECT INTO is minimally logged assuming proper trace flags are set.
SELECT INTO is most commonly used with intermediate data sets, like #temp tables, or to copy out an entire table like for a backup. INSERT INTO is used when you insert into an existing table with a known structure.