这样一个问题:
先创建带主键的表
create table aa(a char(5),b char(5),c char(5),constraint int1 primary key(a,b) using index);
然后插入数据
insert into aa
select '11111' as a,'22222' as b,'33333' as c from dual
union all select '11111' as a,'22222' as b,'44444' as c from dual
union all select '22222' as a,'22222' as b,'44444' as c from dual ;
其中第一第二条违反主键约束的。(插入的都是表,不是一条一条插入的)现在要把违反约束的给忽略掉,其他的插入。
应该怎么写呢?
用异常?游标?
给个详细方法啊 我比较菜
先创建带主键的表
create table aa(a char(5),b char(5),c char(5),constraint int1 primary key(a,b) using index);
然后插入数据
insert into aa
select '11111' as a,'22222' as b,'33333' as c from dual
union all select '11111' as a,'22222' as b,'44444' as c from dual
union all select '22222' as a,'22222' as b,'44444' as c from dual ;
其中第一第二条违反主键约束的。(插入的都是表,不是一条一条插入的)现在要把违反约束的给忽略掉,其他的插入。
应该怎么写呢?
用异常?游标?
给个详细方法啊 我比较菜
alter table aa disable constraint int1;
或者删掉
alter table aa drop constraint int1;
如果你想忽略掉的话直接分开写就是:
insert into aa
select '11111' as a,'22222' as b,'33333' as c from dual;
insert into aa
select '11111' as a,'22222' as b,'44444' as c from dual ;
insert into aa
select '22222' as a,'22222' as b,'44444' as c from dual ;
exception
when DUP_VAL_ON_INDEX then null;
end;
这样能保证违反约束的记录只有一条插入,不过用游标这样做速度会很慢
insert into aa
select a,b,max(c) from (
select '11111' as a,'22222' as b,'33333' as c from dual
union all select '11111' as a,'22222' as b,'44444' as c from dual
union all select '22222' as a,'22222' as b,'44444' as c from dual) t group by t.a,t.b having count(*)=1;留一条c最大的:
insert into aa
select a,b,max(c) from (
select '11111' as a,'22222' as b,'33333' as c from dual
union all select '11111' as a,'22222' as b,'44444' as c from dual
union all select '22222' as a,'22222' as b,'44444' as c from dual) t group by t.a,t.b;
数据库版本?10G以上的话LZ去搜索一下"oracle log error".可以很好的解决这个问题.
正确数据进目的表,有错误的进日志表.其实这个问题很普遍,特别是做ETL的时候.