Bulk insert with insert ... select : Data Insert « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Data Insert

Bulk insert with insert ... select

    
SQL>
SQL> create table EMP (
  2   EMPNO NUMBER(8),
  3   ENAME VARCHAR2(20),
  4   HIREDATE DATE,
  5   SAL NUMBER(7,2),
  6   DEPTNO NUMBER(6) );

Table created.

SQL>
SQL>
SQL> create table DEPT (
  2   DEPTNO NUMBER(6),
  3   DNAME VARCHAR2(20) );

Table created.

SQL>
SQL>
SQL> alter table EMP add constraint EMP_PK primary key (EMPNO);

Table altered.

SQL>
SQL>
SQL> alter table DEPT add constraint DEPT_PK primary key (DEPTNO);

Table altered.

SQL>
SQL> create table myTable ( x varchar2(10));

Table created.

SQL>
SQL> begin
  2       for i in 1 .. 200 loop
  3          insert into myTable values ('x');
  4       end loop;
  5   end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL> insert into EMP select rownum, 'Name'||rownum, sysdate+rownum/100, dbms_random.value(7500,10000), dbms_random.value(1,10) from myTable
  2  where rownum <= 500;

200 rows created.

SQL>
SQL> insert into DEPT select rownum, 'Dept'||rownum from myTable where rownum <= 10;

10 rows created.

SQL>
SQL> drop table EMP;

Table dropped.

SQL> drop table DEPT;

Table dropped.

SQL> drop table myTable;

Table dropped.








Related examples in the same category

1.Use in parameter to pass value and insert value to a table
2.Data insert in a procedure
3.Insert data in procedure
4.Insert value passed in by parameter to a table
5.Insert value to a table after calculation
6.Insert value to product and productcategory with stored procedure
7.Insert value to table with for loop
8.Insert a specified number of suppliers and products per supplier
9.Insert 100000 rows into a table with for loop
10.Loop through all to do a bulk insert
11.An anonymous block program to write the record to a row
12.This script demonstrates returning clause
13.Hard code value and insert