MSSQL INSERT OR UPDATE if EXISTS

Question

Need to insert a row if its not exist and update if exists. I've found this solution for MySQL:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    

name="A", age=19

But I can't find similar for MSSQL..

Lannielanning

Have you had a look at MERGE (Transact-SQL)? – Oppugnant

That syntax is a MySQL quirk. The standard and far more powerful way is to use MERGE – Minority

Answer 1

You can use 2 statements (INSERT + UPDATE) in the following order. The update won't update anything if it doesn't exist, the insert won't insert if it exist:

UPDATE T SET
    name = 'A',
    age = 19
FROM
    [table] AS T
WHERE
    T.id = 1

INSERT INTO [table] (
    id,
    name,
    age)
SELECT
    id = 1,
    name = 'A',
    age = 19
WHERE
    NOT EXISTS (SELECT 'not yet loaded' FROM [table] AS T WHERE T.id = 1)

Or a MERGE:

;WITH ValuesToMerge AS
(
    SELECT
        id = 1,
        name = 'A',
        age = 19
)
MERGE INTO 
    [table] AS T
USING
    ValuesToMerge AS V ON (T.id = V.id)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        id,
        name,
        age)
    VALUES (
        V.id,
        V.name,
        V.age)
WHEN MATCHED THEN
    UPDATE SET
        name = V.name,
        age = V.name;

Mutate

Answer 2

I prefer checking the @@ROWCOUNT. It's a much more compact solution.

UPDATE table set name = 'A', age = 19 WHERE id = 1;
IF @@ROWCOUNT = 0
INSERT INTO table (id, name, age) VALUES(1, "A", 19);

Kerf

Because you're setting the values directly in the UPDATE clause you're potentially opening yourself up to SQL injection possibilities. The second half avoids this with the use of the VALUES method. – Myosin

This is just an example. In production code 'A' would become a variable @a. – Kerf

Might want a transaction around it, and swap the UPDATE vs INSERT guided by which operation is the more prevalent activity you encounter. If INSERTS are occurring more often, then put the INSERT test first. If you are most often updating existing rows, then have that UPDATE statement first. sqlperformance.com/2020/09/locking/upsert-anti-pattern – Epistaxis