普通的insert 操作,通常只能向一个表中插入数据。
使用multitable insert可以实现一条sql语句同时向多个表中插入数据。
下面用例子证明:
表S中有4条数据,如下所示:
select * from s;
ID NAME
1 1 wb
2 2 yf
3 3 wzx
4 4 wzh
为了后面练习 ,根据表表S创建空表S1,S2,S3,S4:
create table s1 as select * from s where 1=2;
create table s2 as select * from s where 1=2;
create table s3 as select * from s where 1=2;
create table s4 as select * from s where 1=2;
下面通过multitable insert语句 将表S中的数据根据id值的不同,分别插入到不同的表:
INSERT FIRST WHEN id = 1 THEN INTO s1
VALUES
(id, name) WHEN id = 2 THEN INTO s2
VALUES
(id, name) WHEN id = 3 THEN INTO s3
VALUES
(id, name) WHEN id = 4 THEN INTO s4
VALUES
(id, name)
SELECT id, name from s;
commit;
执行成功后发现4条数据插入到4个不同的表。通过如下语句可执行相应的操作。
select * from s1;
select * from s2;
select * from s3;
select * from s4;
问题来了,能否将数据插入到视图呢?下面练习证明不可以:
create view vs2 as select * from s2;
INSERT FIRST WHEN id = 1 THEN INTO s1
VALUES
(id, name) WHEN id = 2 THEN INTO vs2 --视图不适用此处
VALUES
(id, name) WHEN id = 3 THEN INTO s3
VALUES
(id, name) WHEN id = 4 THEN INTO s4
VALUES
(id, name)
SELECT id, name from s;
commit;