SQL SELECT INTOStatements

With SQL, you can copy information from one table to another.

The SELECT INTO statement copies data from one table and inserts it into another new table.

SQL SELECT INTO Statement

The SELECT INTO statement copies data from one table and inserts it into another new table.

Note:

MySQL database does not support the SELECT ... INTO statement, but supportsINSERT INTO ... SELECT 。

Of course, you can use the following statement to copy the table structure and data:

CREATE TABLE 新表
AS
SELECT * FROM 旧表 

SQL SELECT INTO Syntax

Suppose there is a table named employees containing the following data:

To create a new table namedemployees_backupand insertemployeesall data from the table into the new table, you can use the following SQL statement:

SELECT *
INTO employees_backup
FROM employees;

After executing this statement, the newemployees_backuptable will contain only data of employees older than 25.

SELECT EmployeeID, FirstName, LastName, Age, Department
INTO employees_backup
FROM employees
WHERE Age > 25;

Usage Notes

Table structure：

• SELECT INTOwill create a new table, and the structure of the new table will be based on the selected columns and data types.
• If the new table already exists,SELECT INTOthe statement will fail. In this case, you can useINSERT INTO ... SELECTstatement.

Database Support：

• SELECT INTOThe statement is very common in SQL Server, but in MySQL and PostgreSQL it is usually usedCREATE TABLE ... AS SELECTstatement.

Alternatives in Other Databases

MySQL and PostgreSQL

In MySQL and PostgreSQL, you can useCREATE TABLE ... AS SELECTto achieve similar functionality:

CREATE TABLE employees_backup AS
SELECT EmployeeID, FirstName, LastName, Age, Department
FROM employees
WHERE Age > 25;