SQL > SQL Commands > Insert Into Select Statement

In the previous section, we learned about how to insert individual values into a table, one row at a time. What if we want to insert multiple rows into a table? In addition to INSERT INTO, we will combine it with the SELECT statement to achieve this goal. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

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

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

Assuming that we have the following tables:,

Table Store_Information

Column NameData Type

Table Sales_Data

Column NameData Type

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

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
SELECT Store_Name, SUM(Sales), Txn_Date
FROM Sales_Information
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.


Assume the Sales_Data table above contains the following data:

Mountain View10150Feb-22-1999
Redwood City10140Feb-22-1999
Redwood City12020Feb-22-1999
Mountain View12035Feb-23-1999
Mountain View10130Feb-23-1999
Redwood City10140Feb-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_Information WHERE Txn_Date = 'Feb-22-1999';
d) INSERT INTO Store_Information (Store_Name, Sales, Txn_Date) SELECT Store_Name, Sales, Txn_Date FROM Sales_Information 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?


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