先表table1 中的数据结构是这样 id A B C D E F 有大批量的数据,要把table1中的数据全部转存到table2 table3 table4 中, table2的结构是 id2 A B table3结构: id3 C D table4 结构: id4 E F 请问怎么写这个sql脚本
insert into table2(id2, a, b) select id, a, b from table1; insert into table3(id3, c, d) select id, c, d from table1;insert into table4(id4, e, f) select id, e, f from table1;commit;
字段类型要匹配: insert into table2 select id, a, b from table1; insert into table3 select id, c, d from table1; insert into table4 select id, e, f from table1;
3楼正解。 to LZ:无法使用一句SQL来插入多个表的。
INSERT ALL INTO TABLE2 VALUES(ID,A,B) INTO TABLE3 VALUES(ID,C,D) INTO TABLE4 VALUES(ID,E,F) SELECT ID,A,B,C,D,E,F FROM TALBE1;
INSERT INTO table2 SELECT id,A,B FROM table1; INSERT INTO table3 SELECT id,C,D FROM table1; INSERT INTO table4 SELECT id,E,F FROM table1;
7楼方法很好,子查询插入到多张表中 insert会自动找到into表中字段列,并且在相应列插入,有点像c++、java中的重载的应用 练习过这样的例题: insert all into e1 values(ename,sal,hiredate) into e2 values(ename,deptno,mgr) select ename,sal,hiredate,deptno,mgr from emp where deptno=10;insert first when sal>3000 then into e1 values(ename,sal,hiredate) when sal>2000 then into e2 values(ename,deptno,mgr) select ename,sal,hiredate,deptno,mgr from emp;
select id, a, b from table1;
insert into table3(id3, c, d)
select id, c, d from table1;insert into table4(id4, e, f)
select id, e, f from table1;commit;
insert into table2 select id, a, b from table1;
insert into table3 select id, c, d from table1;
insert into table4 select id, e, f from table1;
to LZ:无法使用一句SQL来插入多个表的。
INTO TABLE2 VALUES(ID,A,B)
INTO TABLE3 VALUES(ID,C,D)
INTO TABLE4 VALUES(ID,E,F)
SELECT ID,A,B,C,D,E,F FROM TALBE1;
INSERT INTO table3 SELECT id,C,D FROM table1;
INSERT INTO table4 SELECT id,E,F FROM table1;
insert ALL|first [when ... then] into ..., [else ...] select ...
insert first相当于每个when子句后有break;
insert会自动找到into表中字段列,并且在相应列插入,有点像c++、java中的重载的应用
练习过这样的例题:
insert all
into e1 values(ename,sal,hiredate)
into e2 values(ename,deptno,mgr)
select ename,sal,hiredate,deptno,mgr from emp where deptno=10;insert first
when sal>3000 then
into e1 values(ename,sal,hiredate)
when sal>2000 then
into e2 values(ename,deptno,mgr)
select ename,sal,hiredate,deptno,mgr from emp;