SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT statement is used to insert data into a table from the result of a SELECT statement.

This statement can be used to insert data from one table into another table, or to insert data into a table from the result of a query.

In this guide, we will cover the basics of the INSERT INTO SELECT statement and provide examples of how to use it.

Syntax

The basic syntax for the INSERT INTO SELECT statement is as follows:

INSERT INTO destination_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE conditions;
  • The destination_table is the table that you want to insert the data into.
  • The column1, column2, column3, ... are the columns that you want to insert data into.
  • The source_table is the table that you want to select data from.
  • The WHERE conditions are optional and are used to filter the data that is being selected.

Examples

Inserting data from one table into another table:

INSERT INTO backup_employees (employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM employees;

Inserting data into a table from the result of a query:

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
SELECT employee_id, first_name, last_name, hire_date
FROM temp_employees
WHERE hire_date > '2020-01-01';

Inserting data from one table into another table with specific columns:

INSERT INTO backup_employees (employee_id, first_name, last_name)
SELECT employee_id, first_name, last_name
FROM employees;

Inserting data into a table and giving new values to specific columns:

INSERT INTO employees (employee_id, first_name, last_name, hire_date, department)
SELECT employee_id, first_name, last_name, hire_date, 'IT' as department
FROM temp_employees
WHERE hire_date > '2020-01-01';

Consideration

  • The number of columns in the SELECT statement should match the number of columns in the INSERT INTO statement.
  • The datatype of the selected columns should match the datatype of the columns in the destination table.
  • The SELECT statement should return at least one row.
  • It is important to remember that the INSERT INTO SELECT statement will insert all rows returned by the SELECT statement into the destination table.

In conclusion, the SQL INSERT INTO SELECT statement is a powerful tool that allows you to insert data into a table from the result of a SELECT statement.

By using this statement, you can easily insert data from one table into another table, or insert data into a table from the result of a query.

With the help of examples and considerations, you can now confidently use the INSERT INTO SELECT statement in your SQL queries.

SQL Basics