How to add if/else condition in SQL Server query?

Question

I have to parse a json like this:

[
    { "id": 2, "name": "John", "age": 25 },
    { "id": 5, "name": "Jane", "age": 18 }
]

I have to parse it and check if the name already exists in userInfo table. If the name is found in that table, then I only update the age.

Else, I have to insert the data into the userInfo table.

Here's what I did so far:

DROP TABLE IF EXISTS #tmp

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "name": "John", "age":25},
  {"id": 5, "name": "Jane", "age": 18} 
]';

SELECT [id], [info] 
INTO #tmp
FROM OPENJSON(@json)
WITH
    ([id] NVARCHAR(100), 
     [name] NVARCHAR(100),
     [age] Int);

SELECT * FROM #tmp

Now I want to insert or update these data in table userInfo.

If the name from #tmp table already exists in userInfo table, then update the age, else insert the data into the userInfo table.

I don't know what the syntax for this operation is, or if it is possible to do.

Why not do this logic this in your code

hoangnh
– hoangnh

2020-03-12 07:40:20 +00:00
Commented Mar 12, 2020 at 7:40 — hoangnh
– hoangnh, Commented Mar 12, 2020 at 7:40
@hoangnh cause I've to do this inside stored procedure

Araf
– Araf

2020-03-12 07:43:05 +00:00
Commented Mar 12, 2020 at 7:43 — Araf
– Araf, Commented Mar 12, 2020 at 7:43

DhruvJoshi · Accepted Answer · 2020-03-12 07:48:51Z

2

You can use MERGE keyword for this

MERGE userInfo t 
    USING #tmp s
ON (s.name = t.name)
WHEN MATCHED
    THEN UPDATE SET 
        t.age = s.age
WHEN NOT MATCHED THEN 
         INSERT (name, age)
         VALUES (s.name, s.age);

Just to be more succinct I'd do the same this way

MERGE userInfo t 
        USING (SELECT *
FROM OPENJSON(@json)
WITH
(
[id] NVARCHAR(100), 
[name] NVARCHAR(100),
[age] Int
)) s
    ON (s.name = t.name)
    WHEN MATCHED
        THEN UPDATE SET 
            t.age = s.age
    WHEN NOT MATCHED THEN 
             INSERT (name, age)
             VALUES (s.name, s.age);

edited Mar 12, 2020 at 7:48

answered Mar 12, 2020 at 7:40

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

How to add if/else condition in SQL Server query?

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