September 1, 2011 at 5:22 pm
Hi all,
I am looking to use the Merge statement for ETL processes, and I am having a hard time understanding how to use this. The target table contains data specific to each business day. The source contains data for only one day, and the insert, update, delete in the merge needs to apply only to that same day found in the target. My problem is that data from dates not found in the source (past dates) is getting deleted. I only want to apply changes to the one date in question.
Example:
The source contains 100 rows of data only specific to 8/25/2011
The target contains 1000 rows and contains the same columns as the source. Since it is loaded each day on a per day basis, then historically for the last 10 days (since 8/15/2011) this data was stored in the target.
On 8/25, I may run this process more than once on a given day, so logic for all insert, update, and delete is required.
So far, I have:
MERGE #Holdings AS T
USING #HoldingsStage AS S
ON
(
T.AsOfDate = S.AsOfDate
AND T.SecurityID = S.SecurityID
AND T.SourceAcctID = S.SourceAcctID
AND T.Source = S.Source
)
WHEN NOT MATCHED BY TARGET
THEN
INSERT (AsOfDate, SecurityID, SourceAcctID, Source, TransNum, AccruedIncome)
VALUES (S.AsOfDate, S.SecurityID, S.SourceAcctID, S.Source, S.TransNum, S.AccruedIncome)
WHEN MATCHED
THEN
UPDATE SET TransNum = S.TransNum ,AccruedIncome = S.AccruedIncome
WHEN NOT MATCHED BY SOURCE
THEN DELETE
This is my first go at merge statements, and I can't get my head wrapped around this. Thanks in advance...
Sharon
September 1, 2011 at 8:53 pm
Remove:
WHEN NOT MATCHED BY SOURCE
THEN DELETE
Believe that is the source of the unwanted deletions.
September 2, 2011 at 2:36 am
In this posting I presented an example on how to use a cte as the target for merge. The cte can be used to filter the rows to act upon to just those that match your input, i.e. you will delete those row that are no longer in your etl input, but you won't delete the rows that are not in your input because they are "out-of-range" of your etl process.
September 3, 2011 at 11:00 pm
Thank you both Ron and SSC-Enthusiastic. I see it now.
I was able to filter down further by modifying to:
WHEN NOT MATCHED BY SOURCE AND T.AsOfDate = @Date
THEN DELETE
Though I can see where the CTE method could come in handy sometimes, however for this example it was just as easy to do what I did.
Thanks again!
Sharon
September 4, 2011 at 8:20 am
Sha_
Thanks you for replying and posting your modification of the DELETE clause .... now others with a similar problem can learn from what you have done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply