SQL Bulk Upload, Merge and Delete records

merge sql sqlbulkcopy sql-server-2008-r2

Question

I am creating an ASP.net web application, which receives a CSV file from the user, uploads the file to the server, bulk copies the data into a TempHoldingTable. Then the code calls a stored procedure which contains Merge statement to copy the data from the TempHoldingTable to the relevant multiple tables. I am using SQL Server 2008 R2.

I have multiple Merge statements in the stored procedure, I have copied just one below:

MERGE Client AS C
USING (SELECT ClientID, Value1, Value2, Value3, Row_Number() Over (PARTITION BY ClientID order by Date desc) as Rno 
       FROM TempHoldingTable) AS T ON (C.ClientID = T.ClientID)

WHEN NOT MATCHED BY TARGET AND T.Rno = 1
    THEN 
        INSERT(ClientID, Value1, Value2, Value3)
        VALUES(T.ClientID, Value1, Value2, Value3)                      
        OUTPUT $action, inserted.*, deleted.*;

The CSV file is uploaded every month so lets suppose the first file was uploaded in Jan 2014 and it has following data:

ClientID Value1 Value2 Value3
 111      abc    def    ghi
 222      jkl    mno    pqr

The user then uploads second file in Feb 2014 and it has following data:

ClientID Value1 Value2 Value3
 111      aaa    bbb    ghi
 222      jkl    mno    pqr
 333      sss    ttt    uuu

The Merge routine will update the client table for ClientID 111 because the value1 & value2 have been changed, leave the ClientID 222 as it is and insert a new row for ClientID 333.

My question is if user decides to delete the Feb 2014 file, how can I track and revert the changes which were caused due to Feb 2014 upload so that the Client table contains the same data that it had after Jan 2014 upload.

Please note that this tracking needs to be done for each upload so that whenever a user deletes a file the database reverts to the same position as it was in the previous month.

My second question is how to amend the Merge statement to update values if the ClientID exist and any column has different value then the previous one.

Thanks for your patience with reading all this and any help would be greatly appreciated.

Accepted Answer

Answer to Q1: Based on the size of your data, I think setting up a tracking mechanism (Change tracking /CDC) will be an overkill (IMO). You could probably setup a process that (before applying the file) pushes the data from each of the 8 tables into another set of tables(versioned dataset) that stores it based on month/year. If the user decides to delete a particular month file, pull out the previous set from the Versioned Dataset and apply it on the active tables.

Remember to setup a purge process for the versioned dataset though or else it can grow pretty big overtime.

Answer Q2: You could use the When Matched clause

MERGE Client AS C
USING (SELECT ClientID, Value1, Value2, Value3, Row_Number() Over (PARTITION BY ClientID order by Date desc) as Rno 
       FROM TempHoldingTable) AS T ON (C.ClientID = T.ClientID)

WHEN NOT MATCHED BY TARGET AND T.Rno = 1
    THEN 
        INSERT(ClientID, Value1, Value2, Value3)
        VALUES(T.ClientID, Value1, Value2, Value3)                      
WHEN MATCHED 
        THEN UPDATE SET C.Value1 = T.Value1
        , C.Value2 = T.Value2
        , C.Value3 = T.Value#3
OUTPUT $action, inserted.*, deleted.*;



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why