SQL INSERT INTO SELECT
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.
The syntax for INSERT INTO SELECT is as follows:
INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
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.
We use the following tables for our example.
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
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.
ExercisesAssume the Sales_Data table above contains the following data:
1. Which of the following SQL statement is valid? (There may be more than one answer)
2. What data is inserted into the Store_Information table by the following
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?