Monday , September 23 2019
Home / SQL / INSERT rows from SELECT statement

INSERT rows from SELECT statement

You can insert rows into a table from the result of SELECT statement by querying rows.

Syntax:

INSERT  INTO  table_name   (column_1, column_2, column_3)

SELECT   exp_1, exp_2, exp_3 FROM source_table_name

WHERE   conditions;

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.

source_table_name

This is the name of the source table which query results will be stored.

conditions

This is the condition in the SELECT statement that should be met to collect rows from source table.

Example:

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

(

Employee_Id                 varchar2(10),

Employee_Name          varchar2(100),

Address                           varchar2(200),

Join_Date                      date,

Salary_Amount            number(10,2)

);

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

(

Employee_Id                 varchar2(10),

Employee_Name          varchar2(100),

City                                  varchar2(200),

Join_Date                      date

);

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

WHERE Salary_Amount>10000;

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.

Here,

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.

Exercise:

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.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Leave a Reply

Your email address will not be published. Required fields are marked *