Basic Oracle knowledge-advanced insert statements

 

Use the emp, dept, and other tables under the scott user as an example, and use sqlplus for demonstration.

 

 

 

First, create two blank e1, e2:

 

SQL code

SQL> create table e1 as select ename, sal, hiredate from emp where 1 = 2;

 

The table has been created.

 

SQL> create table e2 as select ename, deptno, mgr from emp where 1 = 2;

 

The table has been created.

 

SQL> create table e1 as select ename, sal, hiredate from emp where 1 = 2;

 

The table has been created.

 

SQL> create table e2 as select ename, deptno, mgr from emp where 1 = 2;

 

The table has been created.

 

There are many ways for oracle to insert data into the table

 

1. insert into xxx values (xxx );

 

SQL code

SQL> insert into e1 values ('test', 2000, to_date ('201312', 'yyyymmdd '));

 

One row has been created.

 

SQL> insert into e1 values ('test', 2000, to_date ('201312', 'yyyymmdd '));

 

One row has been created.

 

2. insert into xxx select xxx from xxx;

 

SQL code

SQL> insert into e1 select ename, sal, hiredate from emp;

 

14 rows have been created.

 

SQL> insert into e1 select ename, sal, hiredate from emp;

 

14 rows have been created.

 

3. Advanced inert statement: insert the results of a query statement into multiple tables.

 

1). insert all

 

SQL code

SQL> insert all

2 into e1 values (ename, sal, hiredate)

3 into e2 values (ename, deptno, mgr)

4 select * from emp;

 

28 rows have been created.

 

SQL> insert all

2 into e1 values (ename, sal, hiredate)

3 into e2 values (ename, deptno, mgr)

4 select * from emp;

 

28 rows have been created. Insert each row of the query result to table e1 and Table e2. Select * from emp has 14 rows of data, so 28 rows are inserted.

 

 

2). insert first

 

SQL code

SQL> insert first

2 when sal> 2500 then

3 into e1 values (ename, sal, hiredate)

4 when sal> 1000 then

5 into e2 values (ename, deptno, mgr)

6 select * from emp;

 

12 rows have been created.

 

SQL> insert first

2 when sal> 2500 then

3 into e1 values (ename, sal, hiredate)

4 when sal> 1000 then

5 into e2 values (ename, deptno, mgr)

6 select * from emp;

 

12 rows have been created. Only one row of the query results can be inserted into one table, even if both tables meet the conditions. Insert conditions first... you can continue the test:

 

SQL code

SQL> delete from e1;

 

Five rows have been deleted.

 

SQL> delete from e2;

 

Seven rows have been deleted.

 

SQL> insert first

2 when sal> 1000 then

3 into e1 values (ename, sal, hiredate)

4 when sal> 2000 then

5 into e2 values (ename, deptno, mgr)

6 select * from emp;

 

12 rows have been created.

 

SQL> select count (1) from e1;

 

COUNT (1)

----------

12

 

SQL> select count (1) from e2;

 

COUNT (1)

----------

0

 

SQL> delete from e1;

 

Five rows have been deleted.

 

SQL> delete from e2;

 

Seven rows have been deleted.

 

SQL> insert first

2 when sal> 1000 then

3 into e1 values (ename, sal, hiredate)

4 when sal> 2000 then

5 into e2 values (ename, deptno, mgr)

6 select * from emp;

 

12 rows have been created.

 

SQL> select count (1) from e1;

 

COUNT (1)

----------

12

 

SQL> select count (1) from e2;

 

COUNT (1)

----------

0

 

We can see that no row is inserted in Table e2, because all rows meeting the second condition meet the first condition, so they are all inserted into Table e1. So you need to pay attention to the writing conditions...

 

 

 

OK ~ End ~

By firecym