语句形式为：insert into table2(field1,field2,...) value1,value2,... from table1
要求目标表table2必须存在，由于目标表table2已经存在，所以我们除了插入源表table1的字段外，还可以插入常量
sql>
sql>
sql> create table employee (
2 id number,
3 name varchar(100),
4 birth_date date,
5 gender varchar2(30) );
table created.
sql>
sql>
sql> insert into employee (id,name,birth_date,gender )
2 select 200,'chris',null,'male' from dual;
1 row created.
sql>
sql>
sql> drop table employee;
table dropped.
select into from语句
语句形式为：select vale1, value2 into table2 from table1
要求目标表table2不存在，因为在插入时会自动创建表table2，并将table1中指定字段数据复制到table2中。示例如下：
带有条件选择where
sql>
sql> create table employee (
2 id number,
3 name varchar(100),
4 birth_date date,
5 gender varchar2(30) );
table created.
sql>
sql> insert into employee (id,name,birth_date,gender ) select 300,'h',null,'male' from dual d
2 where not exists (select 1 from employee x where x.id = '300' );
1 row created.
sql>
sql> select * from employee;
id
------
name
----------------------------------------------------------------------
birth_date gender
---------- ------------------------------
300
h
null male
insert bulk by insert ... into ... select
sql>
sql> create table project (
2 pro_id number(4),
3 pro_name varchar2(40),
4 budget number(9,2),
5 constraint project_pk primary key (pro_id)
6 );
table created.
sql>
sql>
sql> insert into project(pro_id, pro_name, budget)values (1001, 'a',12345);
1 row created.
sql> insert into project(pro_id, pro_name, budget)values (1002, 'erp',23456);
1 row created.
sql> insert into project(pro_id, pro_name, budget)values (1003, 'sql',34567);
1 row created.
sql> insert into project(pro_id, pro_name, budget)values (1004, 'crm',45678);
1 row created.
sql> insert into project(pro_id, pro_name, budget)values (1005, 'vpn',56789);
1 row created.
sql>
sql>
sql> set echo on
sql> insert into project (pro_id, pro_name)
2 select pro_id+8000,
3 substr(pro_name,1,31) || ' overhead'
4 from project;
5 rows created.