best implementation of UPDATE-INSERT 1

[alfredjp]

in regards to the thread about implementing oracle cursors in sql server thread183-538939 im posting that what follows as a new thread:

anyway, what i would like to achieve is an "update-insert" process.

taking hint from SQLSister, take an example wherein we have the following code:

CREATE PROCEDURE prc_TestUpdateInsert
AS
  -- update process first
  UPDATE T_Destination_Table
  SET
    Product_Name = Src.Record_Name,
    Product_Value = Src.Record_Value
  FROM
    T_Source_Table AS Src
  WHERE
    Src.Record_ID = T_Destination_Table.Product_Code

  -- insert process second
  INSERT INTO T_Destination_Table (
    Product_Code,
    Product_Name,
    Product_Value
  )
  SELECT
    Src.Record_ID,    -- Record_ID maps to Product_Code
    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
GO

as one would note the above "UPDATE" would AFFECT all the rows in the destination table. fairly ok if we are dealing with few records, but what IF there are so many records in the table.

is there a possible way of only updating the records that has actually changed?

using some sort of an "archived" flag on the source table wouldn't work because that particular table is located on a system that i have "no-touch" privileges.

 

[tlbroadbent]

The process will only update destination rows where the tables match. Updating all rows in the destination willonly occur if the source table contains the same rows.

You can use the columns you are updating as the semaphore to avoid updating columns that don't need to be updated. Not the use of the IsNull function. You may need to change the 2nd parameter if dealing with numeric columns.

-- update process first
UPDATE T_Destination_Table
SET
Product_Name = Src.Record_Name,
Product_Value = Src.Record_Value
FROM T_Destination_Table dst
JOIN T_Source_Table AS Src
ON dst.Product_Code = Src.Record_ID
WHERE Isnull(dst.Product_Name,'') <> IsNull(Src.Record_Name,'')
AND IsNull(dst.Product_Value,'') <> IsNull(Src.Record_Value,'')


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[alfredjp]

....
WHERE Isnull(dst.Product_Name,'') <> IsNull(Src.Record_Name,'')
    AND IsNull(dst.Product_Value,'') <> IsNull(Src.Record_Value,'')

i tried the code, and wouldn't you think that an OR would be better... say, a product could change how much it cost (product_value) without changing its name, right?

anyway, thanks a lot for that particular way of doing it... it really gave me more insight on trying out other situations..

 

[tlbroadbent]

You are correct. The OR is more appropriate in this case.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm