SQL > SQL Commands > Insert Into Select Statement

The INSERT INTO SELECT statement is used to add multiple new records into a database table at one time.

Syntax

The syntax for INSERT INTO SELECT is as follows:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";

Note that this is the simplest form. The SELECT statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.

Please note the number of columns specified in the SELECT statement must be the same as the number of columns specified in the INSERT INTO statement.

Example

We use the following tables for our example.

Table Store_Information

 Column Name  Data Type 
 Store_Name  char(50) 
 Sales  float 
 Txn_Date  datetime 

Table Sales_Data

 Column Name  Data Type 
 Store_Name  char(50) 
 Product_ID  integer 
 Sales  float 
 Txn_Date  datetime 

Table Sales_Data has detailed sales information, while table Store_Information keeps summarized data on sales by store by day. To copy data from Sales_Data to Store_Information, we type in:

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
SELECT Store_Name, SUM(Sales), Txn_Date
FROM Sales_Data
GROUP BY Store_Name, Txn_Date;

Please note that we specified the order of the columns to insert data into in the example above (the first column is Store_Name, the second column is Sales, and the third column is Txn_Date). While this is not absolutely necessary, it is a good practice to follow, as this can ensure that we are always inserting data into the correct column.

Exercises

Assume the Sales_Data table above contains the following data:

 Store_Name  Product_ID  Sales  Txn_Date 
 Mountain View  101 50  Feb-22-1999 
 Cupertino  120 35  Feb-22-1999 
 Redwood City  101 40  Feb-22-1999 
 Sunnyvale  80 60  Feb-22-1999 
 Redwood City  120 20  Feb-22-1999 
 Sunnyvale  101 30  Feb-22-1999 
 Mountain View  120 35  Feb-23-1999 
 Cupertino  80 25  Feb-23-1999 
 Mountain View  101 30  Feb-23-1999 
 Cupertino  120 40  Feb-23-1999 
 Redwood City  101 40  Feb-23-1999 
 Sunnyvale  80 50  Feb-23-1999 

1. Which of the following SQL statement is valid? (There may be more than one answer)
a) INSERT INTO Store_Information SELECT * FROM Sales_Data WHERE Txn_Date = 'Feb-22-1999';
b) INSERT INTO Store_Information SELECT Store_Name, Sales, Txn_Date FROM Sales_Data WHERE Txn_Date = 'Feb-22-1999';
c) INSERT INTO Store_Information (Sales, Txn_Date, Store_Name) SELECT SUM(Sales), Txn_Date, Store_Name FROM Sales_Data WHERE Txn_Date = 'Feb-22-1999';
d) INSERT INTO Store_Information (Store_Name, Sales, Txn_Date) SELECT Store_Name, Sales, Txn_Date FROM Sales_Data WHERE Product_ID BETWEEN 80 AND 100;

2. What data is inserted into the Store_Information table by the following SQL statement?
INSERT INTO Store_Information SELECT Store_Name, SUM(Sales), Txn_Date FROM Sales_Data WHERE Product_ID < 101 GROUP BY Store_Name, Txn_Date;

3. Write a SQL statement that retrieves all sales data from the Sales_Data table and store total daily store sales data in the Store_Information table. How many rows are inserted?

Next: SQL UPDATE

This page was last updated on May 23, 2022.




Copyright © 2022   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact