Oracle SELECT INTO and INSERT into SELECT Two table copy statements in detail

We often encounter situations that require table duplication, such as copying part of a table1 data into table2, or copying the entire table1 to table2, when we are going to use SELECT into and the INSERT into SELECT table copies the statement.

1.INSERT into SELECT Statement

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

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

(2) Note the primary KEY constraint of Table2, if Table2 has a primary key and is not empty, then field1, field2 ... The primary key must be included in the

(3) Note the syntax, do not add values, and insert a data SQL mixed, do not write:

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

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

+ Expand Sourceview plaincopy to Clipboardprint

--1. Creating 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. Creating 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 SELECT statement to copy table data
Insert into Table2 (A, C, D) select a,c,5 from Table1

--4. Displaying the updated results
SELECT * FROM Table2

--5. Deleting a test table
Drop TABLE Table1
Drop TABLE Table2

2.SELECT to from Statement

The statement form is: SELECT vale1, value2 into Table2 from Table1

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:

View Plaincopy to Clipboardprint?
--1. Creating a Test table
Create TABLE Table1
(
A varchar (10),
b varchar (10),
C varchar (10)
)

--2. Creating 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 to from statement CREATE table Table2 and copy data
Select A,c to Table2 from Table1

--4. Displaying the updated results
SELECT * FROM Table2

--5. Deleting a 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, because of the difference between PL/SQL and T.
The sentence in T-SQL is normal, but PL/SQL explains:
Select: into was part of PL/SQL language which means you had to use it inside a PL/SQL block. You cannot use the IT in a SQL statement outside of Pl.
That is, it cannot be executed as an individual SQL statement, and is typically used in PL-SQL program blocks (block).

If you want to implement this functionality in PL/SQL, you can use the CREATE TABLE newtable as SELECT * From ... ：
such as: CREATE TABLE newtable as SELECT * from ATable;

NewTable except for the keys , the others are the same as atable.

---------SQL SELECT into syntax introduction
The SQL SELECT into statement can be used to create a backup copy of the table.
SELECT into statement
The SELECT INTO statement selects data from one table and then inserts the data into another table.
The SELECT into statement is commonly used to create a backup copy of a table or to archive records.
SQL SELECT into syntax
You caninsert all columns into a new table：
SELECT * into New_table_name (in externaldatabase) from Old_tablename
or justInsert the desired column into the new table：
SELECT column_name (s) to new_table_name [in Externaldatabase] from Old_tablename
SQL SELECT into instance-make backup copy
The following example makes a backup copy of the "Persons" table:
SELECT * into Persons_backup from Persons
the IN clause can be used to copy a table to another database：
SELECT * into Persons in ' Backup.mdb ' from Persons
If we want to copy some of the fields, we can list them after the SELECT statement:
SELECT Lastname,firstname
Into Persons_backup
From Persons
SQL SELECT into instance-with a WHERE clause
We can also add a WHERE clause.
The following example creates a table named "Persons_backup" with two columns by extracting information from the "Persons" table of people residing in "Beijing":
SELECT lastname,firstname to Persons_backup from Persons WHERE city= ' Beijing '
SQL SELECT into instance-connected table
It is also possible to select data from more than one table.
The following example creates a new table named "Persons_order_backup" that contains the information obtained from the Persons and Orders two tables:
SELECT Persons.lastname,orders.orderno
Into Persons_order_backup
From Persons
INNER JOIN Orders
On persons.id_p=orders.id_p

Oracle SELECT INTO and INSERT into SELECT Two table copy statements in detail