SQL Bulk Upload, Merge and Delete records

merge sql sqlbulkcopy sql-server-2008-r2

Question

I'm developing a web application in ASP.NET that takes a CSV file from the user, uploads it to the server, and then bulk copies the contents into a database.TempHoldingTable . Then the code calls a stored procedure which containsMerge a clause to copy the data from theTempHoldingTable to the pertinent several tables. Using SQL Server 2008 R2, I have.

I have manyMerge I've copied only one of the stored procedure statements 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 submitted each month, therefore let's assume the initial file, which included the following information, was uploaded in January 2014:

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

The user then uploads a second file in February 2014 with the information shown below:

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

Because the values 1 and 2 have changed, the Merge method will update the client database for ClientID 111, keep ClientID 222 alone, and insert a new record for ClientID 333.

My concern is how to monitor and undo changes brought on by the upload of the Feb. 2014 file, in the event that the user chooses to remove it, such that the Client table has the same data as it had after the upload of the Jan. 2014 file.

Please be aware that this tracking must be done for each upload in order for the database to always remain in the same position as it was the previous month when a user deletes a file.

The answer to my second query is how to modify the merge statement to update data when there is an existing ClientID and a column with a different value than the one before.

We appreciate your patience in reading this and would be grateful for any assistance.

1
0
2/5/2015 6:12:02 PM

Accepted Answer

In response to question 1, I believe that putting up a monitoring system (Change tracking /CDC ) will be excessive (IMO). Before applying the file, you could potentially build up a procedure that pushes the data from each of the 8 tables into a different set of tables (a versioned dataset) that organizes it by month and year. Pull the prior set from the Versioned Dataset and apply it to the active tables if the user chooses to remove a certain month file.

To avoid the versioned dataset becoming too large over time, put up a purge mechanism for it.

Answer to Question 2: You may utilize theWhen 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.*;
0
2/5/2015 9:49:09 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow