Copy tables and data && multiple table INSERT statements in Oracle

Creating test tables and test data

CREATE TABLE Test (ID number,name varchar (10));
INSERT into test values (1, ' Liufang ');
INSERT into test values (2, ' Xiaozhang ');
INSERT into test values (3, ' Dawei ');
INSERT into test values (4, ' Laotan ');
INSERT into test values (5, ' Laotan ');
INSERT into test values (6, ' Laotan ');
INSERT into test values (7, ' Dawei ');

1, copy the table and the data in the table, we can use the following statement to achieve

(Note: The Copy table does not contain information such as default values and constraints, you need to re-see default values and indexes, constraint information, and so on when copying tables and data using the following methods)

CREATE TABLE Test2 as SELECT * from Test;

can also be copied on condition.

CREATE TABLE Test3 as SELECT * from Test where name= ' Laotan '

You can also define a table before adding data

CREATE TABLE Test2 as SELECT * from Test where 2=1;

INSERT INTO TEST2 select * from test;

2. Multi-Table INSERT statement

Specify replication of two test tables (Specify column replication) first

CREATE TABLE EMP1 as select Sequen,ename,sal from EMP where 1=2;
CREATE TABLE EMP2 as select Sequen,ename,cid from EMP where 1=2;

Below we use four kinds of multiple table INSERT statements

A, insert all unconditionally insert
Insert All
Into EMP1 (sequen,ename,sal) VALUES (sequen,ename,sal)
Into EMP2 (sequen,ename,cid) VALUES (SEQUEN,ENAME,CID)
Select Sequen,ename,sal,cid from EMP;

Insert all conditionally inserts: There are no conditions above, while inserting data into the table, and the number of data bars in the two tables is also the same, the following is the addition of different conditions to insert

Insert All
When sal>=2000 Then
Into EMP1 (sequen,ename,sal) VALUES (sequen,ename,sal)
When the CID in (on) then
Into EMP2 (sequen,ename,cid) VALUES (SEQUEN,ENAME,CID)
Select Sequen,ename,sal,cid from EMP;

B, insert first insert (before inserting according to the condition is the same data inserted, if you do not want to two tables in the data have duplicate the same can use Insert first)
Insert First
When sal>=2000 Then
Into EMP1 (sequen,ename,sal) VALUES (sequen,ename,sal)
When the CID in (on) then
Into EMP2 (sequen,ename,cid) VALUES (SEQUEN,ENAME,CID)
Select Sequen,ename,sal,cid from EMP;

In the Insert first statement, when a local table meets the criteria, the two tables are not inserted into the drink row, and the table EMP1 does not appear with the same data as EMP2

That's the difference between the two ways of inserting.

Copy tables and data && multiple table INSERT statements in Oracle