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
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
ASKER
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
And Why is it Select Null, would Select ID be better?
Andy
ASKER
This is my current proc:
INSERT INTO bpOutpat(
id,Title,fname1,fname2,sna
Select ID, Title,fname1,fname2,sname,
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
INSERT INTO bpOutpat(
id,Title,fname1,fname2,sna
Select ID, Title,fname1,fname2,sname,
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
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, ......)
@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, ......)
ASKER
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
I can do this with a Cursor, just looking for a set based method.
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, and sorry for delay in responding.
Andy
Andy
IF EXISTS (SELECT NULL FROM secondTable WHERE someContion)
INSERT INTO thirdTable
VALUES ......
ELSE
INSERT INTO secondTable
VALUES ......