SQL SELECT INTO

What is SQL SELECT INTO?

SQL SELECT INTO is a statement that allows you to create a new table by selecting data from an existing table. It copies the data and structure of a source table into a new table, typically for backup, archiving, or data manipulation purposes.

When you would use it

You would use SQL SELECT INTO when you want to:

1. Create a backup: Make a copy of a table's data and structure before making significant changes or running data-modifying operations.

2. Archiving data: Move historical or old data from one table into a new table for archival purposes while keeping the original data intact.

3. Data transformation: Select data from one table, perform transformations or aggregations, and store the results in a new table.

4. Temporary storage: Create temporary tables to store intermediate results during complex data manipulation operations.

5. Data extraction: Extract a subset of data from a larger table and store it in a new table for specific analysis or reporting.

Syntax

The basic syntax for SQL SELECT INTO is as follows:

SELECT * INTO new_table
FROM source_table;

• *: Selects all columns from the source table.
• new_table: The name of the new table to be created.
• source_table: The name of the source table from which data is copied.

Parameter values

• *: You can specify columns to select if you don't want to copy all columns from the source table.
• new_table: Choose a unique name for the new table.
• source_table: Provide the name of the source table containing the data you want to copy.

Example query

Suppose you have a table called "orders" with columns "order_id," "customer_id," and "order_date," and you want to create a backup of this table. You can use the following SQL query:

SELECT * INTO orders_backup
FROM orders;

Example table response

Assuming the "orders" table contains data like this:

| order_id | customer_id | order_date |
| -------- | ----------- | ---------- |
| 1        | 101         | 2023-10-01 |
| 2        | 102         | 2023-10-02 |
| 3        | 103         | 2023-10-03 |

The query mentioned earlier would create a new table "orders_backup" with the same data:

| order_id | customer_id | order_date |
| -------- | ----------- | ---------- |
| 1        | 101         | 2023-10-01 |
| 2        | 102         | 2023-10-02 |
| 3        | 103         | 2023-10-03 |

The new table "orders_backup" is an exact copy of the original "orders" table.

Use cases

• Creating data backups for safety and data recovery purposes.
• Archiving historical data while keeping the current dataset separate.
• Performing data transformations and aggregations in temporary tables.
• Extracting subsets of data for specific analysis or reporting purposes.
• Splitting large tables into smaller, more manageable tables.

SQL languages this is available for

SQL SELECT INTO is available in various relational database management systems (RDBMS) such as:

• SQL Server
• PostgreSQL
• MySQL
• Oracle Database
• IBM Db2
• SQLite
• MariaDB

Please note that the syntax and behavior of SELECT INTO may vary slightly between different database systems, so it's essential to consult the documentation for your specific database system for precise details.