INSERT INTO 表名 SELECT 话语

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 createdSQL> 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 valuesSQL> insert into dept2 select deptno from dept; ORA-00947: not enough values SQL> delete from dept2; 8 rows deletedSQL> alter table dept2 modify DNAME number; Table alteredSQL> insert into dept2 select * from dept; ORA-01722: invalid number

?

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

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

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

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