Solved

insert with cursor

Posted on 2015-01-29
7
125 Views
Last Modified: 2015-02-02
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.
0
Comment
Question by:shwelopo
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40578979
< 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
WHERE t.id IS NULL
GO

Open in new window

0
 

Author Comment

by:shwelopo
ID: 40579007
I need to do it using cursor to fetch the records for validation purposes.  Thank you.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40579014
Define 'validation purposes'.  You've been given a faster way..
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40579272
do you need it in oracle or sql server ?
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40579463
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40579617
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40579625
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

Open in new window

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question