Insert bulk by insert ... into ... select : Insert with subquery « Insert Delete Update « Oracle PL / SQL






Insert bulk by insert ... into ... select

   
SQL>
SQL> CREATE TABLE project (
  2    pro_id              NUMBER(4),
  3    pro_name            VARCHAR2(40),
  4    budget          NUMBER(9,2),
  5    CONSTRAINT project_pk   PRIMARY KEY (pro_id)
  6  );

Table created.

SQL>
SQL>
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, 'A',12345);

1 row created.

SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, 'ERP',23456);

1 row created.

SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, 'SQL',34567);

1 row created.

SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, 'CRM',45678);

1 row created.

SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, 'VPN',56789);

1 row created.

SQL>
SQL>
SQL> SET ECHO ON
SQL> INSERT INTO PROJECT (pro_id, pro_name)
  2     SELECT pro_id+8000,
  3            SUBSTR(pro_name,1,31) || ' Overhead'
  4     FROM project;

5 rows created.

SQL>
SQL>
SQL>
SQL> drop table project ;

Table dropped.

   
    
    
  








Related examples in the same category

1.Perform more complicated inserts using sub-selects
2.Merge into a table
3.Insert into ... select
4.Conditional INSERT Statement
5.To insert records into a table using a subquery:
6.Use bulk collect and rownum to insert first 10 records
7.Adding Multiple Rows to a Table