How does an oracle database copy a table to a new table ?, Oracle Database copy table

How does an oracle database copy a table to a new table ?, Oracle Database copy table

We often encounter situations that require table replication, such as copying some fields of Table 1 to table 2 or copying the entire table 1 to table 2, in this case, we need to use the select into and insert into select table copy statements.

1. insert into select statement

Statement format: Insert into Table2 (field1, field2,...) select value1, value2,... from Table1

Note:

(1) The target table Table2 must exist, and the field and field2.... must also exist.

(2) Pay attention to the primary key constraint of table 2. If Table 2 has a primary key and is not empty, then field1, field2.... must include the primary key.

(3) Pay attention to the syntax. Do not add values or mix it with the SQL statement for inserting a piece of data. Do not write it as follows:

Insert into Table2 (field1, field2,...) values (select value1, value2,... from Table1)

Because Table 2 already exists in the target table, We can insert constants in addition to the field of Table 1 in the source table. Example:

+ 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 ('Qian ', 'asds', '123 ') insert into Table1 values ('sun', 'asds ', '80') Insert into Table1 values ('lit', '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. display the updated result

Select * from Table2

-- 5. Delete the test table

Drop TABLE Table1

Drop TABLE Table2

2. select into from statement

Statement format: SELECT vale1, value2 into Table2 from Table1

The target table 2 does not exist because table 2 is automatically created during insertion and the specified field data in table 1 is copied to table 2. Example:

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 ('Qian ', 'asds', '123 ') insert into Table1 values ('sun', 'asds ', '80') Insert into Table1 values ('lit', 'asds', null)

-- 3. select into from statement to Create Table 2 and copy data

Select a, c INTO Table2 from Table1

-- 4. display the updated result

Select * from Table2

-- 5. Delete the test table

Drop TABLE Table1 click to open the link

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 the T-SQL.

The statement in the T-SQL is normal, but the PL/SQL interpretation is:

Select .. into is part of PL/SQL language which means you have to use it inside a PL/SQL block. You can not use it in a SQL statement outside of PL/SQL.

That is, it cannot be executed as an SQL statement separately. It is generally used in PL/SQL block.

To implement this function in PL/SQL, use Create table newTable as select * from ...:

For example:

create table NewTable as select * from ATable;

NewTable does not have any keys, but other keys are the same as those of ATable. <