Wednesday, June 11, 2014

INSERT INTO

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 Christmas Santa
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 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.