August 16, 2004 at 4:06 pm
Here is my situation: I need to create a stored procedure that will run daily upon receiving a daily file of Updated Data and New Data.
What I first did was copy over everything from a table on Day 0, and say on Day 1, I begin receiving a daily file of records that have been updated/inserted on the day before, Day 0.
My main table has two fields as primary keys, which with this combination, makes them unique. I usually like working w/ one primary key, but this is something new and I thought it was the best design.
So now, I'm trying to created a stored procedure that follows the following psuedo code:
IF Field1 AND Field2 EXISTS IN
(SELECT Field1 AND Field2
FROM tblAllRecords)
THEN UPDATE tblAllRecords
WITH Updated Records
ELSE INSERT INTO tblAllRecords
ALL values
Hope what I am trying to do makes sense. I have a pretty good library of reference books but I can't seem anything to help me out here. Thank you in advance for any and all advice!
August 16, 2004 at 5:54 pm
If I work on the assumption that you have a table 'tblNewRecords' that contains the daily updates, you can work along these lines:
--First do the updates
update all
set all.* = new.* -- fields as required
from tblAllRecords all
inner join tblNewRecords new
on new.field1 = all.field1 and new.field2 = all.field2
--Now the inserts
insert into tblAllRecords(field_list)
select field_list from tblNewRecords new
left outer join tblAllRecords all
on new.field1 = all.field1 and new.field2 = all.field2
where all.field1 is null and all.field2 is null
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 17, 2004 at 1:08 am
I would change the second query like this.
insert into tblAllRecords(field_list)
select field_list from tblNewRecords new
where not exists( select 1 from tblAllRecords where field1 = new.field1 and field2 = new.field2)
Thanks,
Ganesh
Have a nice day, unles you have other plans.
August 22, 2004 at 12:04 am
OR on the assumption that your new daily data file has all the updates and inserts:
Use a DTS package to Truncate yourTable
Then
Import the New/Revised Data
This can be done in one package.
Butch
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply