Oracle 的表复制

Oracle 简单的两种表复制的方法.  Sql如下：

create table target as select* from source ;
insert into target select * from source;  //table already exist

下面分别介绍下这两种方法的简单测试步骤 ：
方法一：
1. Create source table
create TABLE source
     ( 
         id number(4), 
         name varchar(10), 
         age number(3), 
         CONSTRAINT pk_source PRIMARY KEY (id)
      )

2. Insert data
     Insert into source values(1, 'Mark', 24);
     Insert into source values(2, 'Robinson', 27);
     Insert into source values(3, 'Colin', 26);
     Insert into source values(4, 'Scott', 25);  

3. Create target table
create TABLE targ et
     ( 
         id number(4), 
         name varchar(10), 
         age number(3), 
         CONSTRAINT pk_target PRIMARY KEY (id)
      )
   
4. Copy date from source to target

Insert into target(id, name, age) select 100+rownum ,name,age from source where rownum <=6

这里稍微弄复杂点。其实我们在大数据量数据进行测试时候常用。

5. Drop the test tables.
     drop TABLE source;
     drop TABLE target;

方法二 ：
1. Create source table
create TABLE source
     ( 
         id number(4), 
         name varchar(10), 
         age number(3), 
         CONSTRAINT pk_source PRIMARY KEY (id)
      )

2. Insert data
     Insert into source values(1, 'Mark', 24);
     Insert into source values(2, 'Robinson', 27);
     Insert into source values(3, 'Colin', 26);
     Insert into source values(4, 'Scott', 25); 

3 . Copy date from source to target
create table target as select 100+rownum id ,name,age from source where rownum <3;


4. Drop the test tables.
     drop TABLE source;
     drop TABLE target;