INSERT INTO 表名 SELECT 语句

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

 

CREATE TABLE 表名 AS SELECT 语句

SQL> create table dept2 as select * from dept；
 
Table created

SQL> insert into dept2 select * from dept;
 
4 rows inserted
 
SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select * from dept2;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
8 rows selected
  
SQL> insert into dept2 select DEPTNO, DNAME, LOC, LOC from emp;
 
ORA-00913: too many values

SQL> insert into dept2 select deptno from dept;
 
ORA-00947: not enough values
 
SQL> delete from dept2;
 
8 rows deleted

SQL> alter table dept2 modify DNAME number;
 
Table altered

SQL> insert into dept2 select * from dept;
 
ORA-01722: invalid number

 

由上面的使用 INSERT INTO 表名 SELECT 语句可以看出：

    1：结果集中列的个数必须与插入表一致；

    2： 结果集中列的类型必须与插入表一致；

    3： 当 where 条件不满足时，不插入任何数据；