SELECT INTO usage and supported databases

In SQL,SELECT INTOis a common operation used to insert query results into a new table. Its main purpose is to quickly copy table structure and data, and it is suitable for backup, data migration, temporary table creation and other scenarios. Different database management systems (DBMS) pairsSELECT INTOThe support methods are different, this article will discussSELECT INTOThe usage and support situation in different databases.

1. Basic usage of SELECT INTO

SELECT INTOSyntax is usually used to insert query results into a new table. If the target table does not exist,SELECT INTOThe table will be automatically created and the result of the query is inserted into it.

1. Create a new table and insert data

SELECT column1, column2, ...
INTO new_table
FROM old_table
WHERE condition;

new_tableIt is a new table to be created, which will automatically create the corresponding structure based on the columns of the query result.
old_tableIt is the source table, and the query will extract data from this table.
conditionis an optional filter condition to filter data that needs to be inserted into a new table.

2. No filtering conditions

If not specifiedWHEREclause,SELECT INTOAll data in the source table will be copied to the new table.

SELECT *
INTO new_table
FROM old_table;

This query willold_tableAll data in copy tonew_tableand automatically create the table structure.

2. Application scenarios of SELECT INTO

SELECT INTOVery useful in the following scenarios:

Quickly create backups of tables: You can quickly copy the structure and data of one table into another table as a backup or for disaster recovery.

SELECT * INTO backup_table FROM original_table;

Temporary data storage: You can use it when performing complex queries or processing.SELECT INTOStore intermediate results in temporary tables to avoid repeated calculations.

SELECT customer_id, COUNT(*) AS order_count
INTO temp_orders_summary
FROM orders
GROUP BY customer_id;

Data migration: Migrate data from one table to another, especially when filtering, transforming, or cleaning up the data.

SELECT * INTO new_customers
FROM customers
WHERE registration_date > '2024-01-01';

Data Cleaning and Transformation: BySELECT INTOFilter and convert the data in the original data table into a new table. For example, remove unwanted fields or format data when data is cleaned.

SELECT product_id, UPPER(product_name) AS product_name_upper
INTO clean_product_names
FROM products
WHERE product_name IS NOT NULL;

3. Support of SELECT INTO by different databases

AlthoughSELECT INTOis a common SQL operation, but different database management systems vary in support level and syntax details. Here are some common database pairsSELECT INTOSupport status.

1. SQL Server

SQL Server is rightSELECT INTOSupports one of the most complete databases. It allows users to use it directlySELECT INTOCreate a new table and insert the query results.

grammar:

SELECT * INTO new_table FROM old_table;

Features:

Create a new table directly and insert data.
The indexes, constraints, and triggers of the table are not copied, only the table structure and data.
Supports large-scale data insertion, suitable for fast backups and data migration.

2. PostgreSQL

PostgreSQL does not supportSELECT INTOCreate a new table, but it provides similar functionality usingCREATE TABLE ASSyntax instead. andSELECT INTOdifferent,CREATE TABLE ASAllows more precise control and supports data filtering and conversion.

grammar:

CREATE TABLE new_table AS
SELECT * FROM old_table;

Features:

CREATE TABLE ASSupport andSELECT INTOSame function.
Allows users to create new tables through queries and insert query results into new tables.
You can add it in the queryWHEREclause to filter data.

3. MySQL

MySQL is not supportedSELECT INTOUsed to create new tables, but useCREATE TABLE ... ASgrammar. These two syntaxes are functionally equivalent, the difference is thatCREATE TABLE ... ASMore general.

grammar:

CREATE TABLE new_table AS
SELECT * FROM old_table;

Features:

Suitable for quickly creating new tables and inserting data.
The constraints, indexes, etc. of the table will not be copied, only the data and column structure will be copied.
It is more efficient for large-scale data processing.

4. SQLite

SQLite SupportSELECT INTOSyntax, but it is usually recommended to useCREATE TABLE ASSyntax to achieve the same function.

grammar:

CREATE TABLE new_table AS
SELECT * FROM old_table;

Features:

Same as PostgreSQL and MySQL, SQLite usesCREATE TABLE ASto create a new table and insert data.
Suitable for lightweight database and small-scale data operations.

5. Oracle

Oracle does not support it directlySELECT INTOGrammar, but useCREATE TABLE ASto create a new table and insert data.

grammar:

CREATE TABLE new_table AS
SELECT * FROM old_table;

Features:

The syntax is similar to PostgreSQL and MySQL.
The created new table will not contain the index, constraints, etc. of the original table.

4. Limitations and precautions of SELECT INTO

No copying of indexes and constraints:SELECT INTOThe created table does not include the index, foreign key constraints, triggers, etc. of the original table. For tables with complex constraints, indexes and constraints need to be created manually.
Data type support: Different databases support different data types.SELECT INTOThe behavior may be limited by the data type.
Big data set performance: for big data sets,SELECT INTOOperations may affect performance, especially if no optimization is done (such as no indexing is disabled or batch processing is performed).

5. Summary

SELECT INTOIt is a powerful SQL syntax that can quickly copy data and table structures, and is suitable for backup, data migration, temporary data storage and other scenarios. Different database pairsSELECT INTOThe support for different SQL Server is the most direct, while PostgreSQL, MySQL and Oracle useCREATE TABLE ASSyntax to implement similar functions.

In useSELECT INTOAt the time, developers should select appropriate syntax based on the characteristics of the specific database and consider factors such as indexing and constraints to improve operational efficiency and stability. For large-scale datasets, performance optimizations may be required, such as batch insertion or using batch data import tools.

This is the end of this article about the usage of SELECT INTO and the supported databases. For more related content on SELECT INTO usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!