有3张表
表一:table1 字段(id,courseno,coursename)
表二:table2 字段(id,courseno,majorno)
表三:table3 字段(id,courseno,labno)请问各位大侠,如何用一条插入语句,往这三张表插入数据,这三张表的id,courseno都是一样的
表一:table1 字段(id,courseno,coursename)
表二:table2 字段(id,courseno,majorno)
表三:table3 字段(id,courseno,labno)请问各位大侠,如何用一条插入语句,往这三张表插入数据,这三张表的id,courseno都是一样的
into test1(id,name,sex,cj)
into test2(id,name,sex,cj)
into test3(id,name,sex,cj)
select id,name,sex,cj from test ;
into test1(id,name,sex,cj)
into test2(id,name,sex,cj)
into test3(id,name,sex,cj)
select id,name,sex,cj from test ; 依据条件实现: insert all
when id <10 then
into test1(id,name,sex,cj)
when id >10 and id<50 then
into test2(id,name,sex,cj)
when id >50 and id<1000 then
into test3(id,name,sex,cj)
select id,name,sex,cj from test
-- insert one row
insert all
into table1(id,courseno,coursename) values (id,courseno,coursename)
into table2(id,courseno,majorno) values (id,courseno,majorno)
into table3(id,courseno,labno) values (id,courseno,labno)
select 1 id,'001' courseno,'chinese' coursename,'m001' majorno,'l001' labno from dual;
--insert one row
insert all
into table1(id,courseno,coursename) values (id,courseno,coursename)
into table2(id,courseno,majorno) values (id,courseno,majorno)
into table3(id,courseno,labno) values (id,courseno,labno)
select 2 id,'002' courseno,'english' coursename,'m002' majorno,'l002' labno from dual;
--先处理表table1,再把table2 和table3 同时处理:
---存数据的表
create table table4 (id number,courseno varchar2(5),coursename varchar2(20),majorno varchar2(5),labno varchar2(5));insert into table4
select 2 id,'002' courseno,'english' coursename,'m002' majorno,'l002' labno from dual;
--先把数据都放入到table1
insert into table1 select id,courseno,coursename from table4;
------------------------------------------------------------------------------
--再处理table2和table3 ,insert t2 t3
insert all
into table2(id,courseno,majorno) values (id,courseno,majorno)
into table3(id,courseno,labno) values (id,courseno,labno)
select t1.id, t1.courseno, t4.majorno, t4.labno from table1 t1,table4 t4 where t1.id=t4.id;
或
insert all
into table2(id,courseno,majorno) values (id,courseno,majorno)
into table3(id,courseno,labno) values (id,courseno,labno)
select t1.id, t1.courseno,'000' majorno,'100' labno from table1 t1,dual;