sql-servert-sqlsql-server-2022

SQL JSON continue the INSERT even after the error


As expected, following code example 1 throws a primary key violation error in SQL Server table since I am inserting 2 twice in the id column. And the code does not enter any other values (1,3,4,5) into the table.

Question: how can we make this code to, after throwing error, we ignore the duplicate value 2 and continue inserting the other values - as we can do in regular INSERT statements (shown in the second code example below)?

Remarks: primary key example is just for illustration purposes only. The real task is related to continue INSERT operation in SQL JSON even after any SQL errors occur (data type error etc.)

Example with OPENJSON [enters no records if an error occurs]:

CREATE TABLE #t
(
    id int NOT NULL, 
    CONSTRAINT PK_tmpTbl_ID PRIMARY KEY CLUSTERED (id)
)

DECLARE @json nvarchar(max);
SET @json = N'[
 { "id" : 1},
 { "id" : 2},
 { "id" : 3},
 { "id" : 2},
 { "id" : 5}
]';

INSERT INTO #t (id)
    SELECT id
    FROM OPENJSON(@json)
       WITH (id int)

Example of regular INSERT (enters all records other than the one that throws error):

insert into #t(id) values(1)
insert into #t(id) values(2)
insert into #t(id) values(3)
insert into #t(id) values(2) --throws error, and ignored
insert into #t(id) values(5)
id
1
2
3
5

Solution

  • You don't need a built in feature to handle this, you just need to structure your query such that you don't attempt to insert duplicate rows in the first place e.g.

    WITH cte AS (
        SELECT
            id
            -- Use a duplicate identifying and prioritising row number that meets your production needs
            , ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rn
        FROM OPENJSON(@json)
        WITH (id int)
    )
    INSERT INTO #t (id)
    SELECT id
    FROM cte
    WHERE rn = 1
    -- You only need the following line if its possible the id is already in the table from a previous insert.
    AND NOT EXISTS (SELECT 1 FROM #t t where t.id = cte.id);
    

    db<>fiddle