insert into ... select ... from ... where not exists(...); 已存在的部分在not exists中过滤掉
简单函数筛选,not exists和not in都可以
INSERT INTO T(卡号) SELECT 你的卡号最小值+ROWNUM-1 FROM DUAL CONNECT BY 你的卡号最小值+ROWNUM-1<=你的卡号最大值 MINUS SELECT 卡号 FROM T
insert into t_new SELECT '0'||(01050001+ROWNUM-1) FROM DUAL CONNECT BY level<=01080000-01050001 minus select 卡号 from T;
up................. connect by
看你表内是否有其他数据,如果有,可以创建一个临时表,然后对其进行merge into 操作,如果就单纯只是这一笔数据,直接创建一个表,然后将这张表删除,将新建的表rename成你需要的表明即可,用这种方法记得要新建索引等约束条件 create table xx as SELECT to_char(LEVEL, '0000000') lv FROM dual CONNECT BY LEVEL <= 0108000 MERGE INTO a USING b ON (a.xx = b.xx) WHEN NOT MATCHED THEN INSERT VALUES (b.xx)
select ...
from ...
where not exists(...);
已存在的部分在not exists中过滤掉
SELECT 你的卡号最小值+ROWNUM-1 FROM DUAL CONNECT BY 你的卡号最小值+ROWNUM-1<=你的卡号最大值
MINUS
SELECT 卡号 FROM T
SELECT '0'||(01050001+ROWNUM-1) FROM DUAL CONNECT BY level<=01080000-01050001
minus
select 卡号 from T;
up.................
connect by
create table xx as SELECT to_char(LEVEL, '0000000') lv FROM dual CONNECT BY LEVEL <= 0108000
MERGE INTO a
USING b
ON (a.xx = b.xx)
WHEN NOT MATCHED THEN
INSERT VALUES (b.xx)