Oracle INSERT Command

In Oracle PL/SQL, an INSERT statement adds one or more records to any single table in a relational database. The INSERT statement may also be used add rows to the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.

1. Insert with subquery

You can insert data into one table from another table by using select command as a subquery.

SQL> INSERT INTO emp

  2      (empno, ename)

  3      SELECT deptno, dname

  4      FROM dept;

Insert into ... select

SQL> BEGIN
  2       INSERT INTO emp1
  3           SELECT * FROM emp;
  4       COMMIT;
  5  END;
  6  /

PL/SQL procedure successfully completed.

2. Insert With Functions You can use variables such as SYSDATE and USER in your INSERT statements
SQL> BEGIN
&nbsp&nbsp 2&nbsp&nbsp&nbsp&nbsp INSERT INTO emp (empno, hiredate)
&nbsp&nbsp 3&nbsp&nbsp&nbsp&nbsp VALUES (53, SYSDATE);
&nbsp&nbsp 4&nbsp&nbsp&nbsp&nbsp END;
&nbsp&nbsp 5&nbsp&nbsp&nbsp&nbsp /

PL/SQL procedure successfully completed.
3. Date calculation in insert statement
SQL> insert into old_log values ('Completed chapter 1', sysdate - 60 );

1 row created.
4. Use Trunc in insert statement.
SQL> INSERT INTO product VALUES ('S', 45, 1, trunc(sysdate));

1 row created.
5. Use the Insert Statement to Add records to existing Tables.
Examples:
Insert into emp values (101,'Sami','G.Manager','8-aug-1998',2000);
If you want to add a new row by supplying values for some columns not all the columns then you have to mention the name of the columns in insert statements. For example the following statement inserts row in emp table by supplying values for empno, ename, and sal columns only. The Job and Hiredate columns will be null.
Insert into emp (empno,ename,sal) values (102,’Ashi’,5000);
Now you want to add rows from old_emp table to emp table. Then you can give the following insert statement.
Insert into emp (empno, ename, sal)
&nbsp&nbsp&nbsp&nbsp select empno, ename, sal from old_emp;
Multitable Insert
Suppose we have two table with the following structure.
Sales_table
&nbsp&nbsp prodid
&nbsp&nbsp prodname
&nbsp&nbsp mon_amt
&nbsp&nbsp tue_amt
&nbsp&nbsp wed_amt
&nbsp&nbsp thu_amt
&nbsp&nbsp fri_amt
&nbsp&nbsp sat_amt

Weekly_Sales_table
&nbsp&nbsp prodid
&nbsp&nbsp prodname
&nbsp&nbsp weekday
&nbsp&nbsp amount

Now we want to add the rows from SALES_TABLE table into Weekly_Sales_Table Table in the following Structure.
To achieve the above we can give a multi table INSERT statement given below.
Insert all
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Mon’,mon_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Tue’,tue_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Wed’,wed_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Thu’,thu_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Fri’,fri_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Sat’,sat_amt)
Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Fri_amt,sat_amt from sales;