How to copy and paste data in a table using SQL INSERT INTO SELECT

The SQL INSERT INTO SELECT statement is used to transfer data from one table to another. However, the new table must already exist. Columns without content in the new table will be assigned the value NULL.

What is SQL INSERT INTO SELECT?

The Structured Query Language allows you to create numerous tables within a database and populate them with data. Sometimes, you may need to transfer all or part of the data from one table to another. Instead of re-entering all entries manually, you can copy and paste the data using the SQL INSERT INTO SELECT statement. This statement copies data according to your specifications and inserts it into the new table without overwriting existing entries. The prerequisite is that the data types of the original and the new table match.

Syntax and function

The syntax of SQL INSERT INTO SELECT differs depending on whether you want to copy all columns from one table to another or only selected columns. Here’s the structure for a complete transfer:

INSERT INTO new_table 
SELECT * FROM old_table 
WHERE condition;

The WHERE condition is optional.

To transfer only select columns, the appropriate syntax is:

INSERT INTO new_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ... FROM old_table 
WHERE condition;

Here you specify the individual columns and then also use the SQL command SELECT to select them. In both cases, it’s important that the table new_table has been created in the database. Additionally, the names of all columns in the old and new tables must match. Columns for which no data is available will contain the value NULL.

Copy and paste entire table

To demonstrate how SQL INSERT INTO SELECT works in practice, we’ll create an example table called ‘Customer_List_2023’. This table contains information on customer numbers, names, locations, and the number of items ordered. It looks like this:

There is a new table with the name ‘Customer_List_2024’. This is currently empty.

To insert all entries from the ‘Customer_List_2023’ table into the new ‘Customer_List_2024’ table, we proceed as follows:

INSERT INTO customer_list_2024 
SELECT * FROM customer_list_2023;

All entries from the old customer list are now also in the new customer list:

Transfer selected columns

However, you may only want to transfer certain columns. For example, if the number of items ordered is not relevant for the new table, you can select specific columns for transfer. Here’s how:

INSERT INTO customer_list_2024 (customer number, name, location) 
SELECT customer number, name, location FROM customer list_2023;

We get a new table:

The article column therefore only contains entries with the value NULL.

Install conditions for the transfer

You can also use a WHERE condition to copy selected rows. For our table, for example, we only want to transfer customers from Liverpool. The appropriate code is this:

INSERT INTO customer list_2024 
SELECT * FROM customer list_2023 
WHERE Location = 'Liverpool';

We get the following table:

Alternatives to SQL INSERT INTO SELECT

An alternative to SQL INSERT INTO SELECT is SELECT INTO. Both statements are used to transfer data from one table to another, acting as part of a larger command. However, while SQL INSERT INTO SELECT requires an existing table for the data transfer, SELECT INTO creates a new table