create table xxx select id from a where id not in (select id from b where x=m)
insert into b(COL1,COL2,COL3,...) select COL1,COL2,COL3,... from a; 把字段列出来,比较保险
给你一个过程: create or replace procedure SP_IMP_YGJBXX_HIST IS /*----------------------------------------------------- founcton:insert data from t_ygjbxx into t_ygjbxx_hist author:Jiezhi date:2002/09/10 ------------------------------------------------------*/ TYPE c_curtype IS REF CURSOR; c_cur c_curtype; str STRING(2000):=''; sql_str STRING(5000):=''; v_col col.cname%TYPE; BEGIN
EXECUTE IMMEDIATE 'delete from t_yg_hist where nf=to_char(sysdate,''yyyy'')'|| ' and yf=to_char(sysdate,''mm'')'; str:=''; OPEN c_cur FOR SELECT cname FROM col WHERE tname='T_YGJBXX'; LOOP FETCH c_cur INTO v_col; EXIT WHEN c_cur%NOTFOUND; str:=str||','||v_col||''; END LOOP; str:=ltrim(str,','); sql_str:='insert into t_yg_hist(nf,yf,'||str||')(select to_char(sysdate,''yyyy''),'|| 'to_char(sysdate,''mm''),'||str||' from t_ygjbxx)'; EXECUTE IMMEDIATE sql_str; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
select id from a where id not in (select id from b where x=m)
select COL1,COL2,COL3,... from a;
把字段列出来,比较保险
create or replace procedure SP_IMP_YGJBXX_HIST IS
/*-----------------------------------------------------
founcton:insert data from t_ygjbxx into t_ygjbxx_hist
author:Jiezhi
date:2002/09/10
------------------------------------------------------*/
TYPE c_curtype IS REF CURSOR;
c_cur c_curtype;
str STRING(2000):='';
sql_str STRING(5000):='';
v_col col.cname%TYPE;
BEGIN
EXECUTE IMMEDIATE 'delete from t_yg_hist where nf=to_char(sysdate,''yyyy'')'||
' and yf=to_char(sysdate,''mm'')';
str:='';
OPEN c_cur FOR SELECT cname FROM col WHERE tname='T_YGJBXX';
LOOP
FETCH c_cur INTO v_col;
EXIT WHEN c_cur%NOTFOUND;
str:=str||','||v_col||'';
END LOOP;
str:=ltrim(str,',');
sql_str:='insert into t_yg_hist(nf,yf,'||str||')(select to_char(sysdate,''yyyy''),'||
'to_char(sysdate,''mm''),'||str||' from t_ygjbxx)';
EXECUTE IMMEDIATE sql_str;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end SP_IMP_YGJBXX_HIST;