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;