DTS job Update

[Catadmin]

Question.

I'm creating a DTS job to pull data from one table and combine totals based on a combined key. This DTS will be copying this information to a new table in a different DB. I am trying to figure out if there is a good way to only pull records that haven't been copied over to the new table or if I should just do a simple Select statement in my job and have it drop & recreate the destination table.

Table contains:
Bill_ID (primary key)
Cust_ID
Trace_ID (Both Cust_id & Trace_ID are combined key)
Amt

There can be multiple records with the same Cust_ID and Trace_ID and I'm summing all the amounts with the same Cust_ID and Trace_ID.

Right now, my query is written similar to

Insert Newtable Bill_id, Cust_id, Trace_id, Sum(Amt) as Total
from OldTable join CustomerTable on
OldTable.Cust_ID = CustomerTable.Cust_ID
where Trace_ID in (Select Trace_ID from Oldtable)
and Cust_ID in (Select Cust_ID from Oldtable)
group by Cust_ID, Trace_ID, Bill_ID

Which I can use with an Append to Destination Table type of DTS job, but what I think is going to happen is that it will append everything in the source table several times over so that I end up after the second run of the job 2 copies of every record except the new ones.

Since the DTS job will be running frequently during the day, it's not something I can edit regularly to put in a new date or datetime to check against. So my two options are to Drop & ReCreate the Destination Database every time or find a way to have the query check in the new DB for existing records.

Can anyone give me any recommendations as to which option I should go for and why the other option would be bad?

Thanks!





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

There is a DTS forum, but maybe there's another alternative, so I'll think some...but going out to dinner.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Let's see if I understand the situation correctly. You have a destination table which is just a summation table in one db (call it S) of a detail table that is stored in a different db (call it D). The amount column in S is computed as the sum of all rows in D with the same Trace_ID. Some of the rows from D are already in S, but others are not. You want to add the new rows in D that aren't in S and you want to update the Amt column in S for those rows that are already in S.
If that's correct then you have several errors in the query that you posted. Before I continue down this track, I'd prefer to have confirmation from you.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Actually, I don't think I'll be needing to update the amount column for the rows already in S. I'll just need to be adding to it. But I cannot add duplicate records (i.e., append records to the table that are already existing in it).

My query is currently written as an Insert without any checks to see if the data is already there. I've also got a Select version of the query, again without checks, hanging around. I know I may need to change that part of it. I just don't know which way to do this DTS job, as an Append or a Drop & Recreate.

Thanks for your assistance.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

Donutman,

Thank you for mentioning the DTS forum. I didn't even see it when I looked at the forums last week. Sorry about that. Will post in the future to that forum for those questions.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

As you said, you cannot append rows with a PK that is already there. So if you insist on just the two alternatives you propose then it has to be a Drop and Recreate. However, it would be more efficient to write a SP that inserted the rows that weren't already represented in S and then update the ones that were. This SP could be run as a scheduled job.
If both dbs are on the same server, then another choice is to use a trigger on the detail table that does the update or insert. That would be the most efficient of all choices, but might be a bit problematic if more than one row is inserted into the detail table at a time. Someone with more expertise with triggers would have to help you with that.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]