Oracle inserts data from a table into another table

1. The following two types can be used in Oracle:

01:
Create table newtable as select * from oldtable; // this parameter is used when no new table is created before replication.
02:
Insert into newtable select * from oldtable; // when a new table newtable has been created

Note: The first method is to copy the table structure, but the primary key is not copied in, so be careful when using it.

2. If you want to copy the table structure easily and quickly without the data in oldtable, you can use the following statement:

Create table newtable as select * from oldtable where 1 = 2; (filter out data)

3. If the newtable and oldtable tables have different structures, you can use the following method:

Create table newtable as select s. c1, s. c2 from oldtable s;

 

4. If you want to rename the newtable column Name:

In oracle:

Create table newtable (id, name1) as select s. c1, s. c2 from oldtable s;

Or

Create table newtable as select s. c1, s. c2 from oldtable s;

In mysql, I am afraid I can only use the second method.

5. If you want to add a part of oldtable data to newtable. You can do this:

Create table newtable as (select * from oldtable where...); // Add a where Filter

6. The most common situation is that the id column is used in the new table, which is different from the old table. Use the following statement (we can create a sequence again)

Create table yang (id, name) as select hibernate_sequence.nextval, t. ename from emp t;

7. Note that the select... into statement cannot be used for table export.