Oracle database "Table copy" insert into select from with CREATE TABLE as SELECT * from two table copy statement differences

CREATE TABLE as SELECT * FROM and insert to select from two table copy statement differences

[SQL]View PlainCopy

1. Create table targer_table as select * from source_table
2. INSERT INTO target_table (column1,column2) select Column1,column2 from source_table

The above two sentences are to insert the record of the source table source_table into the target table target_table, but the two sentences are different.
The first sentence (CREATE table as SELECT * from) requires that the target table target_table not exist because it is created automatically when it is inserted.
The second sentence (insert to select from) requires that the target table target_table exists, and because the target table already exists, we can insert constants, such as SQL statements, in addition to the fields source_table the source table:

[SQL]View PlainCopy

1. INSERT INTO target_table (column1,column2) select column1,5 from source_table

In the example: 5;

Whether the CREATE table as SELECT * from or INSERT into select from, the from behind is the source table (source_table);

1. Insert into Select from statement

The statement form is: Insert into targer_table (field1,field2,...) Select Value1,value2,... from source_table

Requires that the target table targer_table must exist, because the target table targer_table already exists, so we can insert a constant in addition to inserting the field source_table the source table. Examples are as follows:

--1. Creating a Test table:

[SQL]View PlainCopy

1. CREATE TABLE Table1
2. (
3. A varchar (ten) PRIMARY KEY,
4. b varchar (ten),
5. C varchar (TEN)
6. );

[SQL]View PlainCopy

1. CREATE TABLE Table2
2. (
3. A varchar (ten) PRIMARY KEY,
4. C varchar (TEN),
5. d Int
6. );

--2. Creating test data:

[SQL]View PlainCopy

2. Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ');
4. Insert into Table1 values (' money ', ' ASDs ', ' 100 ');
6. Insert into Table1 values (' sun ', ' ASDs ', ' 80 ');
8. Insert into Table1 values (' li ', ' ASDs ', null);

Query target table:

SELECT * FROM Table2

No record;

--3.insert the into SELECT statement to replicate table data:

[SQL]View PlainCopy

1. Insert into Table2 (A, C, D) select a,c,5 from Table1

--4. Display the updated results:

[SQL]View PlainCopy

1. SELECT * from Table2;

[Plain]View PlainCopy

1. A C D
2. 1 Zhao 90 5
3. 2 Money 100 5
4. 3 Sun 80 5
5. 4 Li 5

Note: The value of the D field is all constant 5;

--5. Deleting a test table:

[SQL]View PlainCopy

1. Drop TABLE Table1
2. Drop TABLE Table2

2. CREATE table as SELECT * FROM statement

The statement form is: CREATE TABLE targer_table as SELECT * from source_table;

The target table Table2 is not present because the table Table2 is automatically created when it is inserted, and the specified field data in Table1 is copied to Table2. Examples are as follows:

--1. Creating a Test table:

[SQL]View PlainCopy

1. CREATE TABLE Table1
2. (
3. A varchar (ten) PRIMARY KEY,
4. b varchar (ten),
5. C varchar (TEN)
6. );

--2. Creating test data:

[SQL]View PlainCopy

2. Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ');
4. Insert into Table1 values (' money ', ' ASDs ', ' 100 ');
6. Insert into Table1 values (' sun ', ' ASDs ', ' 80 ');
8. Insert into Table1 values (' li ', ' ASDs ', null);

The--3.create table as SELECT * FROM statement creates tables Table2 and copies the data:

[SQL]View PlainCopy

1. Create table TABLE2 as select * from TABLE1;

--4. Display the updated results:

[SQL]View PlainCopy

1. SELECT * FROM Table2

[Plain]View PlainCopy

1. A B C
2. 1 Zhao ASDs 90
3. 2 Money ASDs 100
4. 3 Sun ASDs 80
5. 4 Li ASDs

--5. Deleting a test table:

[SQL]View PlainCopy

1. Drop TABLE Table1
2. Drop TABLE Table2

Report:

Attention:

The CREATE TABLE targer_table as SELECT * from source_table duplicates the table structure + table data,

The CREATE TABLE targer_table as SELECT * from source_table where 1=2; only the same table structure is created and the table data is not replicated.

Two-point description of the Create table as SELECT statement

SQL > CREATE TABLE emp_copy as SELECT * from EMP where deptno=10;

First, note that no column names are defined in the Emp_copy table, because we get the data from the EMP table in the column, using wildcards, so that Oracle generates the columns in the Emp_copy table as in the EMP table-the same name, the same data type definition.

Second, any SELECT statements that can be emitted in sql*plus can be placed in the CREATE table as SELECT statement, and Oracle automatically obtains the data selected from the EMP table in the Emp_copy table. However, if a specific column list is included in the columns of the SELECT statement, the CREATE TABLE clause lists the columns to be included in the table, in parentheses, for example:

SQL > CREATE Table emp_copy_2 (empno,sal) as select Empno, sal from EMP where deptno=10;

========================================================

CREATE TABLE as Select 2010-04-18 11:39:26

Category: Linux

We all know that create table A as SELECT * from B creates a table that is the same as the B-table structure, but it is best not to create a table like this in real-world applications. The reason is that this creates only the structure of the table, not the default values of the original table.

Plainly, the table structure is out, the default value is not.

In addition, but there is a I to a large table execution create table A as SELECT * from B Time reported a temp table space is insufficient, do not know what reason, record. The next time you find it, deal with it.

Oracle database "Table copy" insert into select from with CREATE TABLE as SELECT * from two table copy statement differences