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 subqueryYou 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
   2     INSERT INTO emp (empno, hiredate)
   3     VALUES (53, SYSDATE);
   4     END;
   5     /
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)
     select empno, ename, sal from old_emp;
Multitable Insert
Suppose we have two table with the following structure.
Sales_table
   prodid
   prodname
   mon_amt
   tue_amt
   wed_amt
   thu_amt
   fri_amt
   sat_amt
Weekly_Sales_table
   prodid
   prodname
   weekday
   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
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Mon’,mon_amt)
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Tue’,tue_amt)
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Wed’,wed_amt)
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Thu’,thu_amt)
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Fri’,fri_amt)
    Into week_sales(prodid,prodname,weekday,amount)
    Values (prodid,prodname,’Sat’,sat_amt)
Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
       Fri_amt,sat_amt from sales;
No comments:
Post a Comment