Using MERGE command with JSON data

Question

Is it possible to update a table directly using JSON, without inserting to a temp table?

Below code inserts data to a temp table and updates the original table.

DECLARE @TempTelecommunicationsNumber AS TABLE (RoleID INT,Number VARCHAR(100),Ext VARCHAR(100))
INSERT INTO @TempTelecommunicationsNumber (RoleID,Number,Ext)
 SELECT     @RoleID, Number,Ext
 FROM       OPENJSON(@TelecommunicationsNumber)
 WITH       (RoleID INT, Number NVARCHAR(256), Ext NVARCHAR(256))

            MERGE Party.TelecommunicationsNumber original
            USING @TempTelecommunicationsNumber modified
            ON (original.RoleID = modified.RoleID)
            WHEN MATCHED
                THEN UPDATE SET
                original.Number = modified.Number,
                original.Ext = modified.Ext
            WHEN NOT MATCHED BY TARGET
                THEN INSERT (RoleID,Number,Ext)
                VALUES (@RoleID,modified.Number,modified.Ext);

Is there a way to update directly as below.

MERGE Party.TelecommunicationsNumber original
USING OPENJSON(@Json) modified
ON (original.RoleID = modified.RoleID)
WHEN MATCHED
    THEN UPDATE SET
    original.Number = modified.Number,
    original.Ext = modified.Ext
WHEN NOT MATCHED BY TARGET
    THEN INSERT (RoleID,Number,Ext)
    VALUES (@RoleID,modified.Number,modified.Ext);

Lukasz Szozda · Accepted Answer · 2019-05-13 05:23:20Z

9

Yes it is possible for instance by using common table expressions:

WITH cte AS (
   SELECT     @RoleID AS RoleID, Number,Ext
   FROM       OPENJSON(@TelecommunicationsNumber)
   WITH       (RoleID INT, Number NVARCHAR(256), Ext NVARCHAR(256))
)
MERGE Party.TelecommunicationsNumber original
USING cte modified
ON (original.RoleID = modified.RoleID)
WHEN MATCHED
    THEN UPDATE SET
    original.Number = modified.Number,
    original.Ext = modified.Ext
WHEN NOT MATCHED BY TARGET
    THEN INSERT (RoleID,Number,Ext)
    VALUES (@RoleID,modified.Number,modified.Ext);

db<>fidde demo

or:

MERGE Party.TelecommunicationsNumber original
USING (SELECT @RoleID AS RoleID, Number,Ext
       FROM   OPENJSON(@TelecommunicationsNumber)
       WITH   (RoleID INT, Number NVARCHAR(256), Ext NVARCHAR(256))) modified
ON (original.RoleID = modified.RoleID)
WHEN MATCHED
    THEN UPDATE SET
    original.Number = modified.Number,
    original.Ext = modified.Ext
WHEN NOT MATCHED BY TARGET
    THEN INSERT (RoleID,Number,Ext)
    VALUES (@RoleID,modified.Number,modified.Ext);

edited May 13, 2019 at 5:23

answered May 13, 2019 at 5:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

3 Comments

Saurabh Chauhan Over a year ago

Could you please help me: I would like to convert HEX to bigint using your above solution (fidde demo). But I don't know the right syntax. I tried several times but got an error.

Lukasz Szozda Over a year ago

@SaurabhChauhan It is beyond scope of this particular answer. Please ask new question, and provide initial and desired output.

Saurabh Chauhan Over a year ago

Could you please help me here: stackoverflow.com/questions/61746221/… I will update the question. Thank you!

Collectives™ on Stack Overflow

Using MERGE command with JSON data

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related