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 com­bin­a­tion with other operators to carry out a variety of op­er­a­tions. SQL SELECT DISTINCT, for example, enables you to remove du­plic­ates. 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 para­met­ers:

• 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 ad­di­tion­ally 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, tem­por­ar­ily store or extract records or subsets. If you remove du­plic­ates in the process using DISTINCT, you can also clean up and stream­line your data.

Some practical uses for SELECT INTO include:

• Copying, ex­tract­ing and trans­fer­ring data for customers, orders, patients or products, in the case of new projects, studies or data migration
• Trend and marketing analyses using ag­greg­ated order or purchase data
• Analysing financial data using the transfer of trans­ac­tion data
• Cleaning customer or employee data by removing du­plic­ates in new tables
• Col­lect­ing and analysing data in data ware­houses
• Col­lect­ing 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 in­form­a­tion 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, Post­gr­eSQL, SQlite:

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

Are there al­tern­at­ives to SQL SELECT INTO?

In addition to SELECT INTO, there are several other options for copying and trans­fer­ring 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 state­ments 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 du­plic­ates. You can combine it with the logical operator ALL to specify that all results, even du­plic­ates, 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.