CODEWITHSUNDEEP
jsonsql-servert-sqlsql-server-2016
Harsha W
Harsha W

Reputation: 3366

Using MERGE command with JSON data

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);

Upvotes: 5

Views: 3370

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

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);

Upvotes: 9

Related Questions