SQL Insert Into Select

The INSERT statement in SQL can be combined with a query (SELECT statement) in order to write out the results into a new table. This can be useful for a Data Analyst to save the results of an ad hoc query into an intermediary or working table, or may also be commonly seen in automated transactional queries writing or appending data to tables at a regular cadence (e.g. daily updates of customer information in a retail database).

In this case, the INSERT statement takes the same form as before, only instead of using VALUES and providing the data in the statement itself, we use SELECT and the result is what is inserted into the destination table. The statement takes the form:

INSERT INTO <table> SELECT <columns> FROM <table>;

Let’s say we wish to save a backup copy of our employees table into a new table called employee_backup. Our query would take the form below:

INSERT INTO employees_backup SELECT * FROM employees;

It should be noted that the employees_backup table in this case must already exist, and match the schema (fields and data types) of the result returned from the SELECT statement.

While here we have shown a very simple example duplicating all records in a table, in practice, INSERT INTO SELECT could be used to write out the results of a more complicated query into a working table. This table could then be queried directly by the user, without worrying about the complexity of the initial SELECT statement used to create it.