9

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..

Dmytro
  • 245
  • 1
  • 5
  • 13
  • 1
    Have you had a look at [MERGE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017)? – Thom A Feb 21 '19 at 09:23
  • That syntax is a MySQL quirk. The standard and far more powerful way is to use [MERGE](https://en.wikipedia.org/wiki/Merge_(SQL)) – Panagiotis Kanavos Feb 21 '19 at 09:30

2 Answers2

14

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;
EzLo
  • 13,780
  • 10
  • 33
  • 38
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);
Neil B
  • 2,096
  • 1
  • 12
  • 23
  • 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. – anakaine Apr 30 '21 at 04:53
  • This is just an example. In production code 'A' would become a variable @a. – Neil B Apr 30 '21 at 10:49