Home Forums SQL Server 2008 T-SQL (SS2K8) Flag field update and insert vs all columns update vs delete and insert RE: Flag field update and insert vs all columns update vs delete and insert

  • Hi,

    What you're currently doing is called a Type 1 update. This is the most easy of all. Although, instead of updating all columns you can look for only the columns that have changed and update them. Something like the following merge statement:

    Merge trgTable trg

    using srcTable src

    ON trg.JoinCol = src.JoinCol

    WHEN NOT matched BY target THEN INSERT (ColA, ColB) VALUES (ColA, ColB)

    WHEN matched AND (trg.ColA <> src.ColA OR trg.ColB <> src.ColB)

    THEN UPDATE SET trg.ColA = src.ColA, trg.ColB = src.ColB

    Your second option: deleting all rows and reloading is practically impossible for larger database. You have reload the dimensions as well as fact tables.This is called full load and takes a lot of time.

    Your first option is called Type 2 change. Irrespective of performance, this is ideal for data warehouse situation.

    Below links might help you.

    http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62063/

    http://svangasql.wordpress.com/2011/12/20/difference-between-full-load-and-incremental-load/

    I think it all depends on your business requirements. If you're not required to maintain history, continue to do what you are currently doing, may be use the query i gave above. If you have to maintain history, you don't have an option but to use your first option.

    Sorry if the answer is too basic knowledge for you!