16 December 2006

On Inserts(Insert All-First) In Oracle

In standart SQL there is a simple insert statement that does insertion in database. Oracle has extra features of insert statements to gain performance. For instance you can make a conditional inserts or make multiple inserts with insert statements:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL> create table t_all( i number, z number );
Table created
SQL> insert into t_all values(1,1);
1 row inserted
SQL> insert into t_all values(2,2);
1 row inserted
SQL> insert into t_all values(3,3);
1 row inserted
SQL> insert into t_all values(4,4);
1 row inserted
SQL> insert into t_all values(5,5);
1 row inserted
SQL> create table t_1(i number, z number );
Table created
SQL> create table t_2(i number, z number );
Table created
SQL> create table t_3(i number, z number );
Table created
SQL> insert all
  2    into t_1 values(i,z)
  3    into t_2 values(i,z)
  4    into t_3 values(i,z)
  5  select * from t_all;

15 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> select * from t_3;
         I          Z
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

SQL> rollback;
Rollback complete
SQL> insert all
  2    when mod(z,4) = 1 then
  3      into t_1 values(i,z)
  4    when mod(z,3) = 1 then
  5      into t_2 values(i,z)
  6    else
  7      into t_3 values(i,z)
  8  select * from t_all;

6 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         1          1
         4          4

SQL> select * from t_3;
         I          Z
---------- ----------
         2          2
         3          3

SQL> rollback;
Rollback complete
SQL> insert first
  2    when mod(z,4) = 1 then
  3      into t_1 values(i,z)
  4    when mod(z,3) = 1 then
  5      into t_2 values(i,z)
  6    else
  7      into t_3 values(i,z)
  8  select * from t_all;

5 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          1
         5          5

SQL> select * from t_2;
         I          Z
---------- ----------
         4          4

SQL> select * from t_3;
         I          Z
---------- ----------
         2          2
         3          3

SQL> rollback;
Rollback complete
SQL> drop table t_all;
Table dropped
SQL> create table t_all(i number, j number, k number, l number);
Table created
SQL> insert into t_all values(1,2,3,4);
1 row inserted
SQL> insert all
  2      into t_1 values(i,j)
  3      into t_2 values(i,k)
  4      into t_3 values(i,l)
  5  select * from t_all;

3 rows inserted
SQL> select * from t_1;
         I          Z
---------- ----------
         1          2

SQL> select * from t_2;
         I          Z
---------- ----------
         1          3

SQL> select * from t_3;
         I          Z
---------- ----------
         1          4

SQL> rollback;
Rollback complete
SQL>

No comments: