Trying to avoid a cursor

I have a stored proc that runs as from a job and inserts any new records into another table. This separation is needed for business reasons.

The requirement has changed in that if the record already exists in the second table we have to write to a third table.

I can do this simply enough with a cursor, but is there a set based way to manage this.

Andy
LVL 3
Andy GreenAsked:
Who is Participating?
 
LowfatspreadCommented:
with sql 2008 you could use the merge statement and specify both of your table requirements ....


in 2005
declare @cnt1 integer,@cnt2 integer

--insert into new table first
Insert into NewTable (your third)
(id,Title,fname1,fname2,sname,dob,Sex,Telephone,Mobile1,Mobile2)
Select ID, Title,fname1,fname2,sname,dob,Sex
  From into_Checkin_Live.dbo.bpoutpat_Stage s
Where  Exists (Select id From bpoutpat Where id = s.id)

select @cnt1=@@rowcount

-- do current insert
INSERT INTO bpOutpat
(id,Title,fname1,fname2,sname,dob,Sex,Telephone,Mobile1,Mobile2)
Select ID, Title,fname1,fname2,sname,dob,Sex
  From into_Checkin_Live.dbo.bpoutpat_Stage s
Where Not Exists (Select id From bpoutpat Where id = s.id)

select @cnt2=@@rowcount

-- check...
print 'processed '+convert(varchar(10), (select count(*) from  into_Checkin_Live.dbo.bpoutpat_Stage) )
print 'bpoutpat '+convert(varchar(10),@cnt2)
print 'new tab '+convert(varchar(10),@cnt1)

 

0
 
tigin44Commented:
a structure like this will help

IF EXISTS (SELECT NULL FROM secondTable WHERE someContion)
    INSERT INTO thirdTable
    VALUES ......
ELSE
    INSERT INTO secondTable
    VALUES ......

0
 
Andy GreenAuthor Commented:
I cant see how this works unless I'm missing something. Should the first Select be firstTable?

And Why is it Select Null, would Select ID be better?

Andy

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Andy GreenAuthor Commented:
This is my current proc:

INSERT INTO bpOutpat(
id,Title,fname1,fname2,sname,dob,Sex,Telephone,Mobile1,Mobile2)

Select ID,      Title,fname1,fname2,sname,dob,Sex,From into_Checkin_Live.dbo.bpoutpat_Stage s
Where Not Exists (Select id From bpoutpat Where id = s.id)

This needs to change such that if the record exists we write to a 3rd table.

Andy
0
 
tigin44Commented:
as I understand bpoutpat_Stage is your second table and bpoutpat is the first so your query will lokk like this

@ID   should be the input to your sp.


IF EXISTS (SELECT NULL FROM bpoutpat_Stage WHERE id=@ID )
    INSERT INTO thirdTable
    VALUES (@ID, ......)
ELSE
    INSERT INTO  bpoutpat_Stage
    VALUES (@ID, ......)
0
 
Andy GreenAuthor Commented:
Thats the problem there is no input to the proc, its just runs and inserts the ID from the Checkin_Live table.

I can do this with a Cursor, just looking for a set based method.

Andy
0
 
Andy GreenAuthor Commented:
Thanks, and sorry for delay in responding.

Andy
0
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.

All Courses

From novice to tech pro — start learning today.