shwelopo
asked on
insert with cursor
I'd like to write an sql sp script that will fetch records from a table and insert into another table if it doesn't exist. Pls help. thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Define 'validation purposes'. You've been given a faster way..
do you need it in oracle or sql server ?
In Oracle it would be something like this:
create or replace procedure myproc
v_junk varchar2(10);
begin
for I in (select col1, col2, col3 from some_table) loop
--do some 'validations'...
if i.col1 = 0 then
v_junk := 'Got zero';
else
v_junk := 'Is OK';
end if;
insert into some_other_table(col1, col2, col3, col4) values(i.col1, i.col2, i.col3, v_junk);
end loop;
end;
/
Since you mention: Doesn't exist.
If that is the ONLY validation necessary, look at the MERGE command.
It will do it all for you in a single SQL statement.
Again, only Oracle.
create or replace procedure myproc
v_junk varchar2(10);
begin
for I in (select col1, col2, col3 from some_table) loop
--do some 'validations'...
if i.col1 = 0 then
v_junk := 'Got zero';
else
v_junk := 'Is OK';
end if;
insert into some_other_table(col1, col2, col3, col4) values(i.col1, i.col2, i.col3, v_junk);
end loop;
end;
/
Since you mention: Doesn't exist.
If that is the ONLY validation necessary, look at the MERGE command.
It will do it all for you in a single SQL statement.
Again, only Oracle.
Complete test case using Oracle's MERGE:
drop table tab1 purge;
drop table tab2 purge;
create table tab1( col1 char(1), col2 char(1));
create table tab2( col1 char(1), col2 char(1));
insert into tab1 values('a','1');
insert into tab1 values('b','2');
insert into tab2 values('a','1');
commit;
create or replace procedure myproc
is
begin
merge into tab2 t2 using
(select col1, col2 from tab1) t1
on (t1.col1=t2.col1 and t1.col2=t2.col2)
when not matched then insert(t2.col1,t2.col2) values(t1.col1,t1.col2);
end;
/
show errors
exec myproc
ASKER