mysql
  1. mysql-insert-into-select
PreviousNext

Insert Into Select - MySQL Queries

In MySQL, the INSERT INTO SELECT statement is used to copy data from one table and insert it into another table. This is a convenient way to transfer large amounts of data between tables or databases. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of using the INSERT INTO SELECT statement in MySQL.

Syntax

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

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
  • table_name: The name of the table you want to insert data into.
  • column1, column2, column3, ...: The columns you want to insert data into. If you don't specify columns, MySQL will attempt to insert data into all columns in the table.
  • source_table: The name of the table you want to copy data from.
  • condition: An optional condition you can add to filter the data you want to copy. If you don't specify a condition, MySQL will copy all data from the source table.

Example

Let's say we have a table called "employees" that has columns for "id", "name", "email", and "salary". We want to copy all records from that table into a new table called "employees_copy". Here's how we can use the INSERT INTO SELECT statement to do that:

INSERT INTO employees_copy (id, name, email, salary)
SELECT id, name, email, salary
FROM employees;

Output

When we run the example code above, MySQL will copy all records from the "employees" table into the "employees_copy" table. If successful, we won't see any output.

Explanation

In the example above, we used the INSERT INTO SELECT statement to copy all records from the "employees" table into the "employees_copy" table. We specified the columns we wanted to copy data into ("id", "name", "email", and "salary"), and we specified the source table ("employees") we wanted to copy data from. Since we didn't specify a condition, MySQL copied all data from the source table.

Use

The INSERT INTO SELECT statement is useful for copying large amounts of data from one table to another. You can also use it to select rows from one table based on a condition and insert those rows into another table.

Important Points

  • The data types of the columns you're inserting data into must match the data types of the columns you're copying data from.
  • The number of columns you're inserting data into must match the number of columns you're copying data from.
  • You can use the INSERT INTO SELECT statement to copy data between tables in different databases.

Summary

In this tutorial, we discussed how to use the INSERT INTO SELECT statement in MySQL queries. We covered the syntax, example, output, explanation, use, and important points of using the INSERT INTO SELECT statement. With this knowledge, you can now copy data from one MySQL table to another using the INSERT INTO SELECT statement.

INSERT IGNORECreate Index
Published on: