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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andy GreenAuthor Commented:
Thanks, and sorry for delay in responding.

Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.