源码-Oracle数据库管理-第十章-数据表操作-Part 2（插入数据）

不可否认，这部分的内容相对生疏一些，竟然有种豁然开朗的感觉。

数据入库的方法：

1. 使用insert into子句插入单条数据

2. 使用子查询向单张表插入多条数据

3. 使用insert into子句同时向多张表插入多条数据

4. 使用子查询同时向多张表插入多条数据

5. 导入数据文件

6. 建表、select for update, 复制、粘贴过来（惊人的好用！）

--TBC 2016-10-09--10.1.4 使用子查询插入多行数据--创建一个books_his2（只复制表结构，不复制数据）CREATE TABLE books_his2 AS SELECT * FROM books WHERE 1=2;select * from books_his2;--使用子查询向books_his2表中插入数据INSERT INTO books_his2 SELECT * FROM books;--指定字段列表的方式INSERT INTO books_his2  (book_id, book_name, cate_id)  SELECT orders_seq.NEXTVAL, book_name, cate_id FROM books;--使用内联视图语法插入数据(这部分还真是不好理解，暂且放着，慢慢消化)INSERT INTO  (SELECT empno, ename, deptno, mgr     FROM emp    WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'NEW YORK')     WITH CHECK OPTION) emp                                         --内联视图emp                                       SELECT orders_seq.NEXTVAL, '张五哥', d.deptno, NULL              --插入员工数据    FROM dept d   WHERE d.loc = 'NEW YORK';      rollback;   select * from empabc;   SELECT * FROM emp;   --由于使用了WITH CHECK OPTION，下面的插入语句会失败INSERT INTO  (SELECT empno, ename, deptno, mgr     FROM emp    WHERE deptno = (SELECT deptno FROM dept WHERE loc = '芝加哥')     WITH CHECK OPTION) empabc                                         --内联视图empabc                                       SELECT orders_seq.NEXTVAL, '刘八女', d.deptno, NULL                 --插入员工数据    FROM dept d   WHERE d.loc = '波士顿';         --10.1.5 向多张表中插入数据--使用INSERT FIRST/ALL插入多表数据--无条件的INSERT ALL--1,创建3个表CREATE TABLE books_cate_1 AS SELECT * FROM books WHERE 1=2;CREATE TABLE books_cate_2 AS SELECT * FROM books WHERE 1=2;CREATE TABLE books_cate_3 AS SELECT * FROM books WHERE 1=2;--同时向3个表中插入数据INSERT ALL INTO books_cate_1VALUES  (orders_seq.NEXTVAL, 'NOSQL开发指南', 1) INTO books_cate_2  (book_id, book_name, cate_id)VALUES  (orders_seq.NEXTVAL, 'SQL调优', 2) INTO books_cate_3  (book_id, book_name, cate_id) SELECT book_id, book_name, cate_id FROM books WHERE cate_id = 3; --查询结果SELECT a.*, 'books_cate_1' as "表"  FROM books_cate_1 aUNION ALLSELECT b.*, 'books_cate_2'  FROM books_cate_2 bUNION ALLSELECT c.*, 'books_cate_3' FROM books_cate_3 c;--同时向3个表中插入记录--如果子查询返回多条记录，则同时向表中插入多条记录INSERT ALL INTO books_cate_1VALUES  (book_id, book_name, cate_id) INTO books_cate_2  (book_id, book_name, cate_id)VALUES  (book_id, book_name, cate_id) INTO books_cate_3  (book_id, book_name, cate_id)SELECT book_id, book_name, cate_id FROM books WHERE cate_id = 1;--删除相关的表BEGINDELETE FROM books_cate_1;DELETE FROM books_cate_2;DELETE FROM books_cate_3;END;/--使用INSERT ALL条件多表插入语句INSERT ALL WHEN cate_id=1 THEN INTO books_cate_1 VALUES(book_id, book_name, cate_id) WHEN cate_id=2 THEN INTO books_cate_2 VALUES(book_id, book_name, cate_id)WHEN cate_id=3 THEN INTO books_cate_3 VALUES(book_id, book_name, cate_id)SELECT book_id, book_name, cate_id FROM books;--使用INSERT FIRST条件多表插入语句INSERT FIRST WHEN cate_id<=3 THEN INTO books_cate_1 VALUES(book_id, book_name, cate_id) WHEN cate_id=2 THEN INTO books_cate_2 VALUES(book_id, book_name, cate_id)WHEN cate_id=3 THEN INTO books_cate_3 VALUES(book_id, book_name, cate_id)SELECT book_id, book_name, cate_id FROM books;