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
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.
Learn SQL Today
Get hands-on experience writing code with interactive tutorials in our free online learning platform.
- Free and fun
- Designed for beginners
- No downloads or setup required