• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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.
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< Air code.  I do my own stunts too. >  This can be performed without a cursor, avoiding all the cursor overhead caused by performing actions Row By Agonizing Row (RBAR)

CREATE PROC p_some_proc() AS

INSERT INTO TargetTable (id, goo, foo, boo) 
SELECT id, goo, foo, boo
FROM SourceTable s
  --  LEFT means all rows from source table will be added... 
   LEFT JOIN TargetTable t ON s.id = t.id
-- WHERE the target.pk is null means only source rows that aren't already in target

Open in new window

shwelopoAuthor Commented:
I need to do it using cursor to fetch the records for validation purposes.  Thank you.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'validation purposes'.  You've been given a faster way..
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Naveen KumarProduction Manager / Application Support ManagerCommented:
do you need it in oracle or sql server ?
James ElliottManaging DirectorCommented:
slightwv (䄆 Netminder) Commented:
In Oracle it would be something like this:

create or replace procedure myproc
     v_junk varchar2(10);
    for I in (select col1, col2, col3 from some_table) loop
        --do some 'validations'...
        if i.col1 = 0 then
               v_junk := 'Got zero';
               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;

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.
slightwv (䄆 Netminder) Commented:
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');

create or replace procedure myproc
	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);

show errors

exec myproc

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now