Select INTO and INSERT into SELECT statement

We often encounter situations where table replication is required, such as copying part of a Table1 data to table2, or copying the entire table1 into table2, where we use the SELECT INTO and INSERT into select tables to copy the statements.

1. INSERT into SELECT statement

Statement as: Insert into Table2 (field1,field2,... ) Select Value1,value2,... from Table1

Note: (1) Require the target table Table2 must exist, and the field field,field2 ... must also exist

(2) Note Table2 PRIMARY KEY constraint, if Table2 has a primary key and is not empty, then field1, field2 ... Must include primary key in

(3) Pay attention to grammar, do not add values, and insert a data of the SQL mixed, do not write:

Insert into Table2 (field1,field2,... ) VALUES (select Value1,value2,... from Table1)

Because the target table Table2 already exists, we can insert constants in addition to the fields that are inserted into the source table Table1. Examples are as follows:

+ Expand Sourceview plaincopy to Clipboardprint

--1. Create a test table

Create TABLE Table1

（

A varchar (10),

b varchar (10),

C varchar (10)

）

Create TABLE Table2

（

A varchar (10),

C varchar (10),

D int

）

--2. Create test data

Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ')

Insert into Table1 values (' money ', ' ASDs ', ' 100 ')

Insert into Table1 values (' Sun ', ' ASDs ', ' 80 ')

Insert into Table1 values (' Lee ', ' ASDs ', null)

SELECT * FROM Table2

--3.insert into a SELECT statement to replicate table data

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

--4. Show updated results

SELECT * FROM Table2

--5. Delete Test table

Drop TABLE Table1

Drop TABLE Table2

2. SELECT into from statement

Statement form: SELECT vale1, value2 into Table2 from Table1

The target table Table2 does not exist because the table Table2 is created automatically at insert time and the specified field data in Table1 is copied to Table2. Examples are as follows:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

View Plaincopy to Clipboardprint?

--1. Create a test table

Create TABLE Table1

（

A varchar (10),

b varchar (10),

C varchar (10)

）

--2. Create test data

Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ')

Insert into Table1 values (' money ', ' ASDs ', ' 100 ')

Insert into Table1 values (' Sun ', ' ASDs ', ' 80 ')

Insert into Table1 values (' Lee ', ' ASDs ', null)

--3.select into from statement creates a table Table2 and copies data

Select A,c into Table2 from Table1

--4. Show updated results

SELECT * FROM Table2

--5. Delete Test table

Drop TABLE Table1

Drop TABLE Table2

Note: If you execute this statement in Sql/plus or Pl/sql, the "ORA-00905: Missing keyword" error is reported, due to the difference between Pl/sql and T-SQL.

This sentence is normal in T-SQL, but it is explained in Pl/sql:

Selectinto is part of Pl/sql language which means your have to use it inside a pl/sql block. You can don't use it in a SQL statement outside of Pl/sql.

That is, it cannot be executed as a separate SQL statement, and is typically used in Pl/sql block.

If you want to implement this feature in Pl/sql, you can use the CREATE TABLE newtable as SELECT * From ... ：

such as: CREATE TABLE newtable as SELECT * from ATable;

Author: 51cto Blog Oracle Little Bastard