Inserting selected rows

When working with data, you may face the challenge of copying it from one table to another: for example, if you want to get rid of the table customers and copy all its data to a new table users. Of course, you can do it manually using simple INSERT INTO queries, but that may result in typos or even data loss. To make the transfer process safer, you can combine two simple statements: INSERT INTO and SELECT. Let's take a closer look at how to work with them!

Inserting selected rows

Let's keep our initial example and consider a table customers with columns customer VARCHAR(40), email VARCHAR(50) and zip_code INT:

We have a new table users with columns user VARCHAR(40), user_email VARCHAR(50), zip_code INT and city VARCHAR(20), that already has one pre-existing row:

Let's move the information from the column customer to the column user, from email to user_email and from zip_code to zip_code. We can use the INSERT INTO SELECT statement to do it.

The simple query below will insert all the needed data to the table users:

INSERT INTO users (user, user_email, zip_code) 
SELECT * FROM customers; 

When we execute this query, our table users will look as following:

Note that the table customers will not change: we're copying rows, not moving them.

As you can see, our new rows were added to the end of the users table same as if we added new information with the simple INSERT INTO query.

INSERT INTO SELECT with WHERE

When we use the INSERT INTO SELECT statement, we can use WHERE in the nested SELECT query.

Now let's add information from the table suppliers with columns supplier VARCHAR(40), city VARCHAR(20), supplier_email VARCHAR(45) and zip_code INT:

Say we need to add only the information about Tomato Inc:

INSERT INTO users
SELECT 
    supplier, 
    supplier_email,
    zip_code,
    city 
FROM 
    suppliers
WHERE
    supplier = 'Tomato Inc'; 

Here we should reorder columns in the SELECT statement or the INSERT INTO statement because the order of columns doesn't match.

After the query execution we will have a new row in the table users:

Notice that here the types of columns user_email and supplier_email aren't the same: VARCHAR(50) for user_email and VARCHAR(45) for supplier_email. We can transfer data because these types are compatible, so remember to check whether the types are compatible in your case because if not, you can get an error.

Conclusion

Here is a brief summary: to transfer data from one table to another, use this query template:

INSERT INTO table1 (column_1, column_2, ..., column_n)
SELECT 
    column_1,
    column_2,
    ...,
    column_n 
FROM 
    table2
WHERE 
    condition;