UPDATE statement with INSERT

  • This works okay, but it's not really what I want... :crazy:

    Can anyone help me with a script?

    IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')

    UPDATE Table1 SET (...) WHERE Column1='SomeValue'

    ELSE

    INSERT INTO Table1 VALUES (...)

    What I want is a script (if possible) that will:

    update table B where A.CUSTOMER_ID = B.CUSTOMER_ID

    When that statement = TRUE

    INSERT values (col_1, col_2, etc...) from TABLE A into TABLE B where B.CUSTOMER_ID = A.CUSTOMER_ID

    When that statement = FALSE

    INSERT values into TABLE B as a new row

    The FALSE should never happen because the TRIGGER is going to create the customer record (TABLE A) FIRST then create the supporting data (TABLE B). Also, the business logic in the application prevents B from happening before A.

  • I think you are pretty much describing exactly the functionality of the MERGE command.

    Look it up in Books OnLine, the help there is comprehensive.

  • No, that is not the merge command OP is describing. OP describes some sort of 2-way merge, updating the first table when there is a match, inserting missing records from either into the other. Merge can only modify one table at a time.

    That said, OP also says that the last situation, where table B is to be inserted into, should never happen. If that is the case, merge can be used. However, once you start using it after reading the documentation, you may find merge isn't as obvious to use as it may seem at first glance: it doesn't support a "where" clause. It took me quite some time to figure out that this can be avoided by putting an updateable common table expression (cte for short) where the target table is normally put.

    For example, in below script I created an instead of trigger that uses a single merge command to implement the same functionality a table normally has, i.e. insert the rows that were inserted, update the rows that were updated and delete the rows that were deleted. This may seem ridiculous, but it is an example. You can do the same thing on a view for example to update multiple tables using a single insert, update or delete statement and then it makes a lot more sense ;).

    The thing to note however in the trigger code is that it defines the merge target as a cte, selecting from the table only those rows that are available in pseudo table "Deleted". Then the "Inserted" pseudo table is used as the source for the merge. Now, any rows that were updated will be matched between source and target, newly inserted rows will not be matched by the target and deleted rows will not be matched by the source.

    use tempdb;

    go

    create table dbo.SomeTable (

    col1 int not null,

    col2 varchar(10) not null,

    col3 datetime not null,

    primary key (col1)

    );

    go

    create trigger tbiud_SomeTable

    on dbo.SomeTable

    instead of insert, update, delete

    as

    begin

    set nocount on;

    with cteTarget as (

    select tbl.col1, tbl.col2, tbl.col3

    from dbo.SomeTable tbl

    inner join Deleted d on (d.col1 = tbl.col1)

    )

    merge into cteTarget trg

    using Inserted src

    on (src.col1 = trg.col1)

    when matched then

    update

    set col2 = src.col2,

    col3 = src.col3

    when not matched by target then

    insert (col1, col2, col3)

    values( src.col1, src.col2, src.col3)

    when not matched by source then

    delete;

    end;

    go

    insert dbo.SomeTable( col1, col2, col3)

    select 1, 'test 1', getutcdate()

    insert dbo.SomeTable( col1, col2, col3)

    select 2, 'test 2', getutcdate()

    update st

    set

    col2 = reverse(col2)

    from dbo.SomeTable st

    where st.col1 = 2;

    delete from dbo.SomeTable

    where col1 = 1;

    select *

    from dbo.SomeTable;

    go

    drop table dbo.SomeTable;

    Hope this helps you in understanding the merge command.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply