dark

How to Use INSERT INTO SELECT SQL Statement

How to Use INSERT INTO SELECT SQL Statement

The INSERT INTO SELECT statement in SQL is used to insert data from one table into another. It is a very useful command for copying data from one table to another, or creating a new table with data from an existing one. In this tutorial, you will learn how to use INSERT INTO SELECT SQL statement.

Syntax

The syntax for INSERT INTO SELECT statement is as follows:

INSERT INTO table_name (column1, column2, ... column_n)
SELECT column1, column2, ... column_n
FROM source_table
WHERE condition;

Here, table_name is the name of the table where you want to insert data, and column1, column2, ... column_n are the columns where you want to insert data. source_table is the name of the table from which you want to copy data. condition is the condition that specifies which rows you want to copy.

Examples

Example 1: Copying all rows from one table to another

Suppose we have two tables employees and new_employees. We want to copy all rows from employees to new_employees.

INSERT INTO new_employees
SELECT *
FROM employees;

In this example, we are copying all columns from employees to new_employees. If you want to copy only specific columns, you can replace * with the column names.

Example 2: Copying specific rows from one table to another

Suppose we have a table employees with columns id, name, age, and salary. We want to copy only those rows where the age is less than 30.

INSERT INTO young_employees (id, name, age, salary)
SELECT id, name, age, salary
FROM employees
WHERE age < 30;

In this example, we are copying only those columns where the age is less than 30. We are also copying the data to a new table young_employees.

Example 3: Creating a new table with data from an existing one

Suppose we have a table employees with columns id, name, age, and salary. We want to create a new table employee_names with only id and name columns.

CREATE TABLE employee_names AS
SELECT id, name
FROM employees;

In this example, we are creating a new table employee_names with only id and name columns. We are also copying data from employees to employee_names.

Troubleshooting tips

  • Make sure the columns in the INSERT INTO statement match the columns in the SELECT statement.
  • Check if the columns have the same data type.
  • Make sure there is enough space in the target table for the data you want to insert.
  • Ensure that the WHERE condition is correct and returns the rows you want to copy.

Conclusion

The INSERT INTO SELECT statement is a powerful tool for copying data from one table to another or creating a new table with data from an existing one. By following the syntax and examples in this tutorial, you should be able to use this command in your SQL queries with ease.

Related tutorials