a twist to the UPDATE-INSERT process

[alfredjp]

another twist to the UPDATE-INSERT process is this...

consider a given as below:

  * T_Source_Table (Record_ID, Record_Name, Record_Value)
  * T_Destination_Table (Product_ID, Product_Code, Product_Name, Product_Value)
  * T_Source_Table.Record_ID maps to T_Destination_Table.Product_Code
  * T_Destination_Table.Product_ID is an automatically generated int (identity)

now,
i would like that for every NEW record in the source table, they would be ADDED to the destination table.
AND, for any record that is updated/changed in the source table, it too would be ADDED to the destination table.

without the benefit of oracle's MINUS operator, this is how i implemented it...

CREATE PROCEDURE prc_TestUpdateInsert_History
AS
  -- insert process ALL new
  INSERT INTO T_Destination_Table (
    Product_Code,
    Product_Name,
    Product_Value
  )
  SELECT
    Src.Record_ID,
    Src.Record_Name,
    Src.Record_Value
  FROM
    T_Source_Table AS Src LEFT JOIN T_Destination_Table
  ON Src.Record_ID = T_Destination_Table.Product_Code
  WHERE
    T_Destination_Table.Product_ID IS NULL

  -- create UNION table to find the UPDATED record
  SELECT
    Product_Code,
    Product_Name,
    Product_Value
  INTO #tmpTableUnion
  FROM
    T_Destination_Table
  WHERE
    Product_ID IN (SELECT MAX(Product_ID)
                    FROM T_Destination_Table
                    GROUP BY Product_Code)
  UNION
  SELECT
    Record_ID AS Product_Code,
    Record_Name AS Product_Name,
    Record_Value AS Product_Value
  FROM
    T_Source_Table

  -- insert UPDATED values
  INSERT INTO T_Destination_Table (
    Product_Code,
    Product_Name,
    Product_Value
  )
  SELECT
    Record_ID,
    Record_Name,
    Record_Value
  FROM
    T_Source_Table
  WHERE
    Record_ID IN (
      -- find the CHANGED RECORD
      SELECT Product_Code AS Record_ID
      FROM #tmpTableUnion
      GROUP BY Product_Code HAVING COUNT(*)>1
    )

GO

would the above be an efficient way of implementing this considering tables with dozens of fields and thousands of records? or is there any other way, if there are, i would really appreciate any comment.

thanks!

 

[wrbodine]

Hi alfredjp,

Another way to implement the above scenario would be to use triggers. You could create an ON INSERT and an ON UPDATE trigger for the Source table, and code the triggers to perform the inserts into the destination tables. Check out BOL (Books Online) for more info on triggers (or feel free to post again for more clarification).

Take it easy,
Ray

P.S. - I don't think the above code would work for the update section where you create a UNION table to find the UPDATED record. A union brings back all rows from each query on either side of it, and your second query would bring back all rows from the source table, definitely not what you want to include in the destination table. I'd go with triggers in this scenario, or find some other way to check which fields have been updated, which I can't think of offhand.

 

[alfredjp]

Another way to implement the above scenario would be to use triggers. You could create an ON INSERT and an ON UPDATE trigger for the Source table, and code the triggers to perform the inserts into the destination tables. Check out BOL (Books Online) for more info on triggers (or feel free to post again for more clarification).

i was planning on using a query, unfortunately, according to the senior designer -- all update procedures between the source table and the destination table occurs only during an evening batch process.

I don't think the above code would work for the update section where you create a UNION table to find the UPDATED record. A union brings back all rows from each query on either side of it, and your second query would bring back all rows from the source table, definitely not what you want to include in the destination table.

my reasoning is this...

the FIRST query of the UNION would get all the LATEST records from the destination table. take note that the Product_ID is actually a HISTORY marker (an indentity column).
the SECOND query would then get ALL the data (including updated data) from the source table.
now, if you UNION them, and assume that one field in one record in the source data (ie., in the SECOND query); you'd find a TWO records with the same Product_Code (in the source table --> Record_ID).
therefore you could then exclude the UPDATED records by using a GROUP BY ... HAVING COUNT(*)>1