SQL Merge a List of Parent Child data

Question

I have a list of data that I am passing to a stored procedure as XML. The data is a list of Widget and a widget contains a list of WidgetItem (parent child data). I would like to do a MERGE based a subset of Widget based on the ParentID. Some data for the ParentID has been updated, some has been removed (so missing from the xml) and some data is new.

The updated data will never need the child data updated as the Widget record can only be adjusted, not the items (child data) inside it. Insert will always have a one or more child records (WidgetItems).

I can't seem to figure out how to do this in a MERGE as that seems like the best approach as compared to handling the merge in the data layer.

Here is what I have so far... I put a comments where I am stuck:

CREATE PROCEDURE dbo.pWidgetsMerge
    @Widgets XML
AS

/*
Assumed XML input @Widgets xml:
<Widgets>
    <Widget>
        <WidgetID>
        <ParentID>
        <StartDate>
        <EndDate>
        <Details>
            <WidgetDetailItem>
                <WidgetDetailItemID>
                <WidgetID>
                <SomeID>                
                <SomeData>
*/

MERGE
    [dbo].[Widget] as w  
USING
    (
        SELECT
            'WidgetID' = P.value('WidgetID[1]', 'INT'),
            'ParentID' = P.value('ParentID[1]', 'INT'),
            'StartDate' = P.value('EffectiveStartDate[1]', 'DATETIME'),
            'EndDate' = P.value('EffectiveEndDate[1]', 'DATETIME')
        FROM
            @Widgets.nodes('/Widgets/Widget') PROPERTYFEED(P)
    ) 
    AS xmlIn
    (
        [WidgetID],
        [StartDate],
        [EndDate]
    )
    ON
        w.[WidgetID] = xmlIn.[WidgetID]
    WHEN
        NOT MATCHED
    THEN
        INSERT 
        (
            [ParentID],
            [StartDate],
            [EndDate]
        ) 
        VALUES
        (
            xmlIn.[ParentID],
            xmlIn.[StartDate],
            xmlIn.[EndDate]
        )


        /*STUCK HERE: After the insert, need to put in the child
            records into a new table [WidgetItems].  Maybe it's another
            operation outside of the merge?*/

    WHEN
        MATCHED AND (
            (w.[StartDate] <> xmlIn.[StartDate]) OR 
            (w.[EndDate] <> xmlIn.[EndDate]))
    THEN
        UPDATE SET
            w.[StartDate] = xmlIn.[StartDate],
            w.[EndDate] = xmlIn.[EndDate]
    WHEN
        NOT MATCHED BY SOURCE AND w.[ParentID] = xmlIn.[ParentID]
    THEN
        UPDATE SET
            w.[DeletedDate] = GETDATE()

Also, if I am approaching this wrong an alternative solution would be appreciated or maybe I do need to handle this at the data layer.

Answer 1

Below is the updated code that should answer your question. I've added comments to explain what's going on. Hopefully it makes sense.

As you stated, ParentID is the same for all Widgets passed in, so I'm treating it as a parameter instead of an element of the XML

DECLARE @ParentID INT = 1

DECLARE @Widgets AS XML = 
N'<Widgets>
    <Widget>
        <WidgetID />
        <StartDate />
        <EndDate />
        <Details>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>4</SomeID>             
                <SomeData/>
            </WidgetDetailItem>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>323</SomeID>             
                <SomeData/>
            </WidgetDetailItem>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>1</SomeID>            
                <SomeData/>
            </WidgetDetailItem>
        </Details>
    </Widget>
    <Widget>
        <WidgetID>10</WidgetID>
        <StartDate>January 1, 2015</StartDate>
        <EndDate />
        <Details>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>4</SomeID>         
                <SomeData/>
            </WidgetDetailItem>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>99</SomeID>         
                <SomeData/>
            </WidgetDetailItem>
            <WidgetDetailItem>
                <WidgetDetailItemID></WidgetDetailItemID>
                <WidgetID/>
                <SomeID>6</SomeID>            
                <SomeData/>
            </WidgetDetailItem>
        </Details>
    </Widget>
</Widgets>';

--Used to hold the pseudoID -> WidgetID relationship for inserting the details
DECLARE @WidgetIds AS TABLE ([Action] varchar(10), PseudoID INT, WidgetID INT);

; 
--Use a CTE of the subset of data to be more performant. If we just went straight to the 
--merge we'd be operating on the entire table and that can have some major performance hits
WITH T AS (
              SELECT 
                     w.* 
              FROM
                     [dbo].[Widget] as w 
              WHERE
                     w.[ParentID] = @ParentID
)
MERGE INTO T 
USING (
        SELECT
            --Generate a pseudoid based on the order of the Widget elements so that we have some way of 
            --linking the detail records to the master
            row_number() OVER(ORDER BY PROPERTYFEED.P) PseudoID,
            'WidgetID' = P.value('WidgetID[1]', 'INT'),
            'ParentID' = @ParentID,
            'StartDate' = P.value('StartDate[1]', 'DATETIME'),
            'EndDate' = P.value('EndDate[1]', 'DATETIME')
        FROM
            @Widgets.nodes('/Widgets/Widget') PROPERTYFEED(P)
    ) 
    AS xmlIn
    (
           [PseudoID],
        [WidgetID],
        [ParentID],
        [StartDate],
        [EndDate]
    )
    ON
        T.[WidgetID] = xmlIn.[WidgetID]
    WHEN
        NOT MATCHED
    THEN
        INSERT 
        (
            [ParentID],
            [StartDate],
            [EndDate]
        ) 
        VALUES
        (
            xmlIn.[ParentID],
            xmlIn.[StartDate],
            xmlIn.[EndDate]
        )
    WHEN
        MATCHED AND (
            (T.[StartDate] <> xmlIn.[StartDate]) OR 
            (T.[EndDate] <> xmlIn.[EndDate]))
    THEN
        UPDATE SET
            T.[StartDate] = xmlIn.[StartDate],
            T.[EndDate] = xmlIn.[EndDate]
    WHEN
        NOT MATCHED BY SOURCE AND T.[DeletedDate] IS NULL 
    THEN
        UPDATE SET
            T.[DeletedDate] = GETDATE()         
OUTPUT  $action, xmlIn.PseudoID, INSERTED.WidgetID INTO @WidgetIds

;

--This is some magic to generate a temp table of numbers from 1 to COUNT(Widget)
--This is so we can reference the parent Widget row in the same order as the pseudoid generated above
--http://stackoverflow.com/a/1134379/4375845
;WITH Total(TotalWidgets) AS (SELECT COUNT(1) TotalWidgets FROM @Widgets.nodes('/Widgets/Widget') PROPERTYFEED(P))
       , Numbers(Num) as (
              SELECT 1 AS Num
              UNION ALL 
              SELECT Num+1 
              FROM Numbers
              JOIN Total t ON 1 = 1
              WHERE Num < t.TotalWidgets )
INSERT INTO WidgetDetailItem (WidgetID,SomeID,SomeData)
SELECT 
       w.WidgetID
       ,Details.SomeID
       ,Details.SomeData
FROM 
    (SELECT 
        P.value('WidgetDetailItemID[1]','int')  WidgetDetailItemID           
        , P.value('SomeID[1]','int') SomeID
        , P.value('SomeData[1]','varchar(5)') SomeData
        , n.Num AS PsuedoID
    FROM Numbers n
    --This is what gives us our pseudo ID to link to the row_number() function from the first merge statement
    CROSS APPLY @Widgets.nodes('/Widgets/Widget[sql:column("n.Num")]/Details/WidgetDetailItem') AS M(P)
    ) Details
JOIN @WidgetIds w on Details.PsuedoID = w.PseudoID
WHERE w.Action = 'INSERT' --We only want inserts by your spec

SELECT * FROM Widget;
SELECT * FROM WidgetDetailItem;

Answer 2

I have de-serialized the incoming XML into a table. That allowed the opportunity to validate the data within the XML string.

Item 1: The new de-serialized table would allow an easy way to filter which data is included in the MERGE.

Item 2: Inserting data into the child table will have to be in a separate call. The MERGE is only able to handle crud (Create Update Delete) operations on one table.

NOTE: The MERGE will use all of the records in the DESTINATION table. So when you are not matched by Source this will act on all records not included in the table.