utilize ident_current in JSON insert

Is it possible to merge / mix / join the SQL command IDENT_CURRENT(‘table_name’) with a OPENJSON insert statement?

For example a table dbo.properties contains:

• id as int (primary key, auto increment)
• objectId as int
• property as varchar(50)
• value as varchar (50)

I fill the property and value based on a JSON and are in need to use IDENT_CURRENT(‘objects’) to fill the objectId column. Currently my statement looks like:

DECLARE @properties nvarchar(max) = N'[{"property": "A", "value": "1"},
                                       {"property": "B", "value": "2"},
                                       {"property": "C", "value": "3"}]';
INSERT INTO dbo.property
SELECT *
FROM OPENJSON(@properties)
WITH ( property varchar(50) '$.property',
       value varchar(50) '$.value');

And want to change the statement to something like:

DECLARE @properties nvarchar(max) = N'[{"property": "A", "value": "1"},
                                           {"property": "B", "value": "2"},
                                           {"property": "C", "value": "3"}]';
    INSERT INTO dbo.property (objectId)
    VALUES (IDENT_CURRENT('nodes')) 
    SELECT *
    FROM OPENJSON(@properties)
    WITH ( property varchar(50) '$.property',
           value varchar(50) '$.value');

Answer

Cannot say I’d necessarily recommend your approach. Generally I’d recommend inserting into both tables together and using SCOPE_IDENTITY() instead. With your approach, if another process inserts a row at the same time, it could cause data issues since your script might grab the wrong identity value

DECLARE @id INT;

BEGIN TRAN

/*
INSERT INTO nodes...
Put your code here that inserts into the nodes table */

SELECT @id = SCOPE_IDENTITY() /*Returns last IDENTITY inserted into nodes. RECOMMEND THIS APPROACH*/
--SELECT @id =IDENT_CURRENT('nodes') /*Using your requested function*/

INSERT INTO property(objectid,property,[value])
SELECT @id,property,[value]
FROM OPENJSON(@properties)
WITH ( property varchar(50) '$.property',
       value varchar(50) '$.value');


COMMIT TRAN