--给你举个例子 --insert into A select B.*,'111' from B;[TEST@orcl] SQL>create table t1(col1 varchar2(10));表已创建。[TEST@orcl] SQL>create table t2(col1 varchar2(10),version varchar2(10));表已创建。[TEST@orcl] SQL>insert into t1 values(1);已创建 1 行。[TEST@orcl] SQL>commit;提交完成。[TEST@orcl] SQL>select * from t2;未选定行[TEST@orcl] SQL>insert into t2 select t1.*,'111' from t1;已创建 1 行。[TEST@orcl] SQL>commit;提交完成。[TEST@orcl] SQL>select * from t2;COL1 VERSION ---------- ---------- 1 111
那你相同的主键的怎么办? 不修改? insert into A select B.*,'111' from B where B.id not in (select A.id from A);
重建A表,最后字段默认值为111,然后执行 insert into A select * from b ;
merge into A using B on(A.主键 = B.主键) when matched then null --这里你可以自己写更新语句 --如:update set A.主键之外的字段1 = B.主键之外的字段1, --A.主键之外的字段2 = B.主键之外的字段2...... when not matched then insert into select B.*,'111' from B;
--假设A表字段col1,col2,Versioninsert into A(col1,col2,Version)
select B.col1,B.col2,'111'
from B;
--给你举个例子
--insert into A select B.*,'111' from B;[TEST@orcl] SQL>create table t1(col1 varchar2(10));表已创建。[TEST@orcl] SQL>create table t2(col1 varchar2(10),version varchar2(10));表已创建。[TEST@orcl] SQL>insert into t1 values(1);已创建 1 行。[TEST@orcl] SQL>commit;提交完成。[TEST@orcl] SQL>select * from t2;未选定行[TEST@orcl] SQL>insert into t2 select t1.*,'111' from t1;已创建 1 行。[TEST@orcl] SQL>commit;提交完成。[TEST@orcl] SQL>select * from t2;COL1 VERSION
---------- ----------
1 111
那你相同的主键的怎么办? 不修改?
insert into A select B.*,'111' from B where B.id not in (select A.id from A);
merge into A
using B
on(A.主键 = B.主键)
when matched then
null
--这里你可以自己写更新语句
--如:update set A.主键之外的字段1 = B.主键之外的字段1,
--A.主键之外的字段2 = B.主键之外的字段2......
when not matched then
insert into select B.*,'111' from B;