Sasha Stecovych Sasha Stecovych - 3 months ago 10x
SQL Question

Insert update based on value

I have a request to modify / insert new data in the products based on Value.

Currently I'm using SSIS to insert data into heap table and then I need to move records to table based on Status.

I have 3 status Add, Update, Remove.

How I can write a insert / update query based on mentioned below statuses?

Here is logic:

  • If Status column contain Add - I need to insert these records in products table

  • If Status column contain Update - I need to update data which already present in Products table

  • If Status column contain Remove - I need to Deactivate product set bit Active column data to 0


Two choices:

  1. Write an update statement to update any existing rows and follow it up with an insert statement that will insert new rows. This will run in two operations but will allow you to maintain the logic completely independently.
  2. Use the merge operator to perform several actions in a single operation.

Wrap 1 or 2 up in a stored procedure and then simply call that procedure from SSIS. Personally, I'm using a version of example 1 where the insert/update code are individually wrapped procedures that the upsert procedure simply calls in the proper order. This is slightly less efficient than example 2 but I have found it to be a more flexible and maintainable pattern (especially when paired with table-valued parameters).

Example 1:

create procedure dbo.usp_MyTable_Upsert
    update <Target>
       set ...
    from <Source>
    join <Target>
        on ...;

    insert into <Target>(...)
    select ...
    from <Source>
    where not exists (
              select 1
              from <Target>
              where ...

Example 2:

create procedure dbo.usp_MyTable_Upsert
    merge <Target>
    using <Source>
        on ...
    when matched then
           set ...
    when not matched by target then
        insert ...
    when not matched by source then