Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

How to Insert, Update, or Delete Data in MS SQL Server

Introduction
INSERT with SELECT
6. Copy data with INSERT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Okay. You already know how to use INSERT to insert rows. You can also use INSERT to copy the 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 only store products that are currently available. Historic products will be stored in the ProductHistory table. To start the table, we want to insert all information from our Product table into this historical table. The statement below will copy all data from Product to ProductHistory:

INSERT INTO ProductHistory 
SELECT * FROM Product;

First, we write INSERT INTO with the name of the table we want to add data to. 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 also have the PurchaseHistory table. Use INSERT INTO ... SELECT to copy all data from the Purchase table to the PurchaseHistory table.