SQL "SELECT INTO" vs "INSERT INTO SELECT" statements.

#sql #database #data #dataengineering

The "SELECT INTO" statement copies data from one table into a new table.
the syntax of the statement is as follows below

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

we can also only copy some columns into the new table

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

The INSERT INTO SELECT statement requires that the data types in source and target tables match.

Note: The existing records in the target table are unaffected.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

we can also Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

SELECT INTO creates a new table while INSERT INTO SELECT requires an existing table.
SELECT INTO is for creating backup or temporary tables while INSERT INTO SELECT is used to transfer data between existing tables.