May 19, 2012 at 5:23 am
Hi folks,
First of all, hello SQLServerCentral users. I have been a member for some time but this is my first post 😀
I could do with some more brain power if you could help with this scenario.
I have three tables designed to track the state of current and historical widgets held in an external system.
The three tables are split by processing functionality:
1. A staging table to land new widgets and/or updated widgets. These will be cleared on each process run.
2. A Master widget table containing a list of all of current widgets with current attributes.
3. A widget history table listing all widget changes that have been loaded over time, with their associated attributes indicating which values have changed since the previous version.
The current design uses SQL Merge (SQL 2008) to determine which records to INSERT/UPDATE from table 1 to table 2. Table 2 contains an INSERT/UPDATE trigger which logs all the changes to table 3 as they occur.
This approach works if table 1 contains no more than one instance of the same widget in one load e.g.
WidgetID, Attribute1, Attribute2, Attribute3, DateTimeStamp
1 A B C '2012-05-19 00:00:00'
2 A C D '2012-05-19 00:00:00'
3 B F G '2012-05-19 00:00:00'
However it is not clear yet if table 1 will be loaded with multiple instance of the same widget e.g.
WidgetID, Attribute1, Attribute2, Attribute3, DateTimeStamp
1 D B C '2012-05-18 00:00:00'
1 A B C '2012-05-19 00:00:00'
2 A C D '2012-05-19 00:00:00'
3 B F G '2012-05-19 00:00:00'
In this scenario, SQL Merge statement doesn't work and throws the error
'Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.'
I can get around this by selecting the latest distinct values from table 1, however I want to log the all of history anyway and this would be skipping some records.
I have tried a set based update method such as
UPDATE T
SET T.Attribute1 = I.Attribute1, T.Attribute2 = I.Attribute2, T.Attribute3 = I.Attribute3
FROM dbo.TargetTable T
INNER JOIN
(
SELECT *
FROM
dbo.ImportTable) I
ON T.ID = I.ID
However, when I look at the output from the UPDATE trigger it appears as though it only registers a single update to the target table (the first match it finds). Is it possible to do such an update in a 'set based' way? I don't really want to have to create cursor and process each imported record one at a time if there is an alternative.
Thanks.
May 19, 2012 at 11:04 am
Please post your merge statement.
May 19, 2012 at 7:46 pm
Hi here it is..
MERGE dbo.TargetTable as Target
USING dbo.ImportTable as Source
ON (source.WidgetID = Target.WidgetID)
WHEN MATCHED
THEN UPDATE SET
Target.Attribute1 = Source.Attribute1,
Target.Attribute2 = Source.Attribute2,
Target.Attribute3 = Source.Attribute3,
Target.DateTimeStamp = Source.DateTimeStamp;
Based on my understanding of MERGE I fully expect it to fail, since the source tables can contain multiple occurances of 'WidgetID' such as '1' appearing twice in my original post. It just so happens that the UPDATE (from table) statement can handle multiple source records to update from, but it is not deterministic so you never quite know which of the many occurances you are updating from.
I have had some good suggestions from some helpful folks on MSDN SQL Forum and will add a summary here, should it be of use to other users here who require a similar loading pattern in future.
1. Loop through the staging table, for each instance of the widget in the table apply MERGE on each pass e.g. if there are 3 occurance of WidgetID 1, then at least three passes of the staging table is required. To do this, add an InstanceNo column to the staging table and populate it with the ROW_NUMBER function, partitioning by the widget ID. This means the trigger logic in the target table is retained and can be used to populate the history table (I will refer to this as logging logic).
OR
2. Load the history table directory from the staged and only apply the latest widget instance to the target table. This does however make the logging logic quite complex and harder to maintain.
If it's useful to other forum members I will post the finished loading pattern once complete.
Thanks
May 19, 2012 at 11:41 pm
Here's what I'd do in this instance:
Set up an audit trigger on table 1 to track all inserts, updates, into it. Insert the data from the trigger into Table 3, designating that it came from Table 1 in a column in Table 3. Then, when something goes live from Table 2, log that in Table 3 as well, and limit it to Merging only the most recent record.
That way, you have a complete history (from Table 1), and you also have a history of what finally makes it into Table 2, all in the same table, Table 3, clearly indicating which are from which source.
That'll handle both of your needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2012 at 6:58 am
MERGE with TRIGGERS will not function as desired in your scenario either. You will need to break the processing into discreet steps - i.e. UPDATE existing matching rows, INSERT new rows. I think the dates can be used to ensure correct processing (UPDATing) of multiple rows, but like you said it will be a PITA.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2012 at 9:19 am
i'd be tempted to build a small loop (pseudo code below)
while records affected>0
begin
merge oldest record for each widget
remove oldest records from staging for each widget
end
in that way your audit trail is preserved and your merge statements are protected - sure you might have to run the merge statement once for each duplicate, but it's proboably not that much overhead - saves you from going into cursor territory and merging each row individually
MVDBA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply