Generate Script with UPDATE if exists else INSERT (MSSQL)

Question

I want to create "UPDATE if exists else INSERT" statements for existing values in a table with the following pattern:

    if exists (select 1 from TABLE_NAME where Ident_Column=Identifier ) 
    update TABLE_NAME set   
        Column1=Value1,
        Column2=Value2,
        Column3=Value3,
        ...
         where Ident_Column=Identifier
    else
        insert into TABLE_NAME ( Column1, Column2, Column3, ...)
        select Value1, Value2, Value3, ...
    go

I try to use the MSSQL Mangement Studio (2014) script generator, but I do not get the output as desired.

EDIT: The desired output IS the SQL statement, @Rusland K. & Nick.McDermaid . I want to use the script generator to generate this SQL code for each (or selected) rows of the table TABLE. Identifier is not a variable, its a value. If the value Identifier in Column Ident_Column exists, set the row accordingly (Value 1-n). If that Identifier is not found in any row, create a now row accordingly. @bmsqldev: This is the entire code (just replaced the real column/table names and the concrete values)

Actually I'm don't understand what caused the missunderstanding here. If you can tell, I'd be happy to learn.

EDIT2: I endet up writing a small script which converts the script output of MSSQL Mangement Studio according to this pattern, which took me about 2 hours.

No need to. You can do this using MERGE. Or simply remove the IF and use the proper WHERE statements in the UPDATE and INSERT clauses. The speed will be the same as MERGE. You could also join the target table with either a source query or TVP to perform the MERGE/UPSERT for all rows at once — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 2, 2016 at 11:17
@RuslanK. MERGE is not buggy although it requires as much coding as the two separate statements. No result is asked so what should it matter? All statements have OUTPUT clauses though — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 2, 2016 at 11:21
@RuslanK. you'll find some of these issues have been fixed but the entry in Microsoft Connect hasn't changed. In any case, writing two separate UPDATE/INSERT statements will work and take the same amount of code. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 2, 2016 at 11:31

Chanukya · Accepted Answer · 2016-03-02 11:34:55Z

0

MERGE TABLE_NAME AS TARGET
    USING(
    SELECT
       *
    FROM
        DBO.TABLE_NAME
    WHERE
       IDENT_COLUMN=IDENTIFIER 

) AS SOURCE
    ON(
    TARGET.COLUMN1 = SOURCE.COLUMN1
    AND TARGET.COLUMN2 = SOURCE.COLUMN2
    AND TARGET.COLUMN3 = SOURCE.COLUMN3

)
WHEN MATCHED THEN UPDATE
SET
        TARGET.COLUMN1=SOURCE.VALUE1,
        TARGET.COLUMN2=SOURCE.VALUE2,
        TARGET.COLUMN3=SOURCE.VALUE3,
    WHEN NOT MATCHED THEN INSERT
        (
            COLUMN1, COLUMN1, COLUMN1, ...)

    VALUES(
        (VALUE1, VALUE2, VALUE3, ...
        ),
        SOURCE.VALUE1,
        SOURCE.VALUE2,
        SOURCE.VALUE3,...
    );;

answered Mar 2, 2016 at 11:34

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate Script with UPDATE if exists else INSERT (MSSQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related