How to use SQL SELECT INTO

You can use SQL SELECT INTO to copy data into new tables or temporary storage areas. This enables quick and reliable data transfers for sorting, updating and cleaning up data sets without gaps.

What is SQL SELECT INTO?

SQL offers a range of tools that you can use to manage, analyse and edit your database. One such tool is SQL SELECT, which you can use in combination with other operators to carry out a variety of operations. SQL SELECT DISTINCT, for example, enables you to remove duplicates. SQL SELECT INTO copies records from one table into another. However, it’s not one of the classic SQL commands, SQL operators or functions – instead it works as a clause in an SQL statement.

What is the syntax of SQL SELECT INTO?

The basic syntax of SQL SELECT INTO looks like this:

SELECT  *
INTO  new_target_table
FROM existing_table;

It uses the following parameters:

• SELECT: Specifies the columns in the existing table that you want to copy data from. You can either enter a specific column or use the asterisk * to copy all the data from the source table.
• INTO: Defines and names the target table for the copied records.
• FROM: Specifies the existing table that you want to copy records out of.
• WHERE: The SQL WHERE clause is optional. You can use it to additionally narrow down which data you want to copy.

What is SQL SELECT INTO used for?

The main use for SQL SELECT INTO is copying data into a new table. It can also be used to aggregate, temporarily store or extract records or subsets. If you remove duplicates in the process using DISTINCT, you can also clean up and streamline your data.

Some practical uses for SELECT INTO include:

• Copying, extracting and transferring data for customers, orders, patients or products, in the case of new projects, studies or data migration
• Trend and marketing analyses using aggregated order or purchase data
• Analysing financial data using the transfer of transaction data
• Cleaning customer or employee data by removing duplicates in new tables
• Collecting and analysing data in data warehouses
• Collecting data for machine learning models

Examples of SQL SELECT INTO

Let’s now look at two examples that show how SQL SELECT INTO works.

Select and transfer distinct customers

Let’s say you want to transfer each unique result (i.e., not including repeat entries) from the column ‘Customer’ in a table called ‘Orders’ into a new table called ‘OrdersNew’. The syntax for that would look as follows:

SELECT DISTINCT Customer
INTO OrdersNew
FROM Orders;

Transfer entries for customers from a specific region

You can further narrow down customer information using a WHERE clause. If you want to transfer all the entries for customers in the UK, that would look as follows:

SQL Server:

SELECT Customer, Country
INTO OrdersNew
FROM Orders
WHERE Country = 'UK';

MySQL, PostgreSQL, SQlite:

INSERT INTO OrdersNew (Customer, Country)
SELECT Customer, Country
FROM Orders
WHERE Country = 'UK';

Are there alternatives to SQL SELECT INTO?

In addition to SELECT INTO, there are several other options for copying and transferring data between tables. Here are some of the most important ones:

• INSERT INTO SELECT: With SQL INSERT INTO SELECT, you can copy and transfer selected records or results from one table into another existing table. Whereas SELECT INTO creates a new table INSERT INTO SELECT works with existing tables.
• CREATE TABLE AS: SQL CREATE TABLE statements are used to create new tables. When combined with SQL SELECT and AS, you can specify that the new table should be based on the results of the SELECT statement and thus contain the data selected from an existing table.
• UNION or UNION ALL: The operator SQL UNION unites records from the selected tables and columns in a new result table without duplicates. You can combine it with the logical operator ALL to specify that all results, even duplicates, should be included.
• IMPORT TABLE: The statement IMPORT TABLE is only suitable if you want to import records from external tables and files. It’s primarily available in newer SQL versions.
• LOAD DATA INFILE: This statement works like IMPORT TABLE but is more flexible and more complex. It has more supported formats for external data sources and is available in all SQL versions.