Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
INSERT with SELECT
6. Copy data with INSERT
Referring to other tables
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Good! Let's get started. You already know how to use INSERT to add rows. You can also use INSERT to copy data from another table into your current table.

A simple way to do this is to include a SELECT in the INSERT clause. The data chosen by SELECT is inserted into the columns given in the INSERT clause. In this case, you don't use the VALUES clause.

The store wants the product table to store only products that are currently available. Discontinued products will be stored in the product_history table. To start populating the product_history table, we want to insert all information from the product table into this historical table. The statement below will copy all data from product to product_history:

INSERT INTO product_history
SELECT * FROM product;

First, we write INSERT INTO and the name of the table receiving the data. After that, we add a SELECT statement to pull the data from the source table, which in this case is the product table. Simple, right?

Exercise

Among our report tables, we have the purchase_history table. Use INSERT INTO ... SELECT to copy all data from the purchase table to the purchase_history table.