You can insert rows into a table from the result of SELECT statement by querying rows.
INSERT INTO table_name (column_1, column_2, column_3)
SELECT exp_1, exp_2, exp_3 FROM source_table_name
is the name of the table in which rows will be inserted.
column_1, column_2, column_3
These are the list of the table columns in which data will be inserted.
exp_1, exp_2, exp_3
These are the list of the expressions which values will be stored into the columns. The value of exp_1 will be stored in the column_1, the value of exp_2 will be stored in the column_2, the value of exp_3 will be stored in the column_3 and so on.
This is the name of the source table which query results will be stored.
This is the condition in the SELECT statement that should be met to collect rows from source table.
To insert rows from the results of SELECT statement, the following steps should be performed.
- First create OLD_Employee table and insert three rows in the table.
CREATE TABLE OLD_Employee
INSERT INTO OLD_Employee VALUES (‘EMP-101′,’ABUL HOSSEN’,’DHAKA’,’02-APR-2007′,8000);
INSERT INTO OLD_Employee VALUES (‘EMP-102′,’ABDUR RAHIM’,’GAZIPUR’,’12-MAY-2009′,12000);
INSERT INTO OLD_Employee VALUES (‘EMP-103′,’JAMAL MIA’,’CHITTAGONG’,’22-APR-2010′,18000);
SELECT * FROM OLD_Employee;
- Socondly create NEW_Employee table and query the table.
CREATE TABLE NEW_Employee
SELECT * FROM NEW_Employee;
- Finally execute the following INSERT statement which will insert rows from SELECT statement.
INSERT INTO NEW_Employee (Employee_Id,Employee_Name,City,Join_Date)
SELECT Employee_Id,Employee_Name,Address,Join_Date FROM OLD_Employee
In the example of INSERT statement, the result of SELECT statement that is from OLD_Employee table whose salary greater than 10000 will be inserted into NEW_Employee table.
The value Employee_Id column of OLD_Employee table will be stored into the Employee_Id column of NEW_Employee table.
The value Employee_Name column of OLD_Employee table will be stored into the Employee_Name column of NEW_Employee table.
The value Address column of OLD_Employee table will be stored into the City column of NEW_Employee table.
The value Join_Date column of OLD_Employee table will be stored into the City column of Join_Date table.
- Now it is time to query the NEW_Employee table to confirm whether rows have been inserted or not.
SELECT * FROM NEW_Employee;
This is how; we can insert rows from the results of SELECT statement.
N.B: First practice following the above steps and then do the exercise with your own knowledge given below.
1) Create a table named EMPLOYEE containing four columns which are Emp_Id, Emp_Name, Emp_City, Emp_Join_Date. Then insert all data into newly created table EMPLOYEE from the table already exist name is NEW_Employee by SELECT statement. Finally query to check whether data have been inserted from the table into your new table.
2) Create a table named OLD_PRODUCT containing four columns are Product_id, Product_Name, Price, Quantity and insert 5 products information.
Then create another table named PRODUCT_INFO along with three columns are Prod_id, Prod_Name, Prod_Type.
Now insert products information of Product_id, Product_Name columns from OLD_PRODUCT table into Prod_id, Prod_Name columns of new table PRODUCT_INFO.
Finally query to check the information in PRODUCT_INFO table.