1

I am writing a stored procedure to regularly copy data from multiple source tables to a destination table. I would like to insert data from one column of a source table into the destination table, and then update the destination table based on that column.

I have written a stored procedure that:

  1. Inserts into the destination table datetime data from a source table, checking to not insert any datetime values that already exist at the destination.
  2. Updates the rest of the destination columns based on the source columns, going back in time far enough that any changes to previous data should be captured. 
BEGIN TRANSACTION; 

    BEGIN TRY

        INSERT INTO DestTbl (DtTm)
        SELECT
            TblA.DtTm
        FROM
            TblA
        WHERE   
                TblA.DtTm > DATEADD(DAY, -1, GETDATE())
            AND TblA.DtTm NOT IN
                (
                    SELECT
                        DestTbl.DtTm
                    FROM 
                        DestTbl
                )
        ORDER BY
            TblA.DtTm ASC;

        COMMIT TRANSACTION;

    END TRY

-- There is a transaction like this for each source table
BEGIN TRANSACTION;

    BEGIN TRY

        UPDATE
            DestTbl
        SET
            DestTbl.Col2 = TblB.SomeCol
        FROM
            DestTbl
        INNER JOIN
            TblB
            ON      
                    TblB.DtTm > DATEADD(DAY, -1, GETDATE()) 
                AND DestTbl.DtTm = TblB.DtTm;

        COMMIT TRANSACTION;

    END TRY

What is the best way to ensure that my INSERT is performed before the UPDATE statement? Or should I just refactor my queries to be like this: https://stackoverflow.com/a/11010548?

Thank you.

VT33
  • 11
  • 2
  • 10
    *"What is the best way to ensure that my INSERT is performed before the UPDATE statement?"* By performing the `INSERT` statement first. SQL is sequential, and will run each statement in the batch top down. IF your `INSERT` statement is first in the batch, it'll have to complete successfully for the `UPDATE` statement afterwards to be executed. – Thom A May 14 '19 at 19:23
  • Is SQL sequential when it comes to INSERT and UPDATE statements specifically? Because I thought that the query optimizer determines the order of execution based on various factors. – VT33 May 14 '19 at 19:33
  • 3
    It determines the order of execution within a single statement. It does NOT decide to change the order of statements. Meaning it isn't going to evaluate your procedure and decide to run the update statement first. – Sean Lange May 14 '19 at 19:44
  • Side note: Using a single statement inside a transaction is redundant. SQL Server runs each statement inside an implicit transaction anyway. – Zohar Peled May 16 '19 at 12:04

0 Answers0