Processing data row by row while bulk insert using JSON in SQl server

Question

Need to Generate Tag Number While Inserting Data using JSON in SQl Server Stored Procedure.

I have 2 Tables Table1: Category (lastusedIndex null in initial stage)

Id	categoryname	assetPrefix	lastusedIndex
1	Keyboard	KYB	Null
2	Desktop	DTP	Null

Table 2: asset (Inserting data into this table)

It has following column

Id, AssetTag, SerialNumber, categoryid

I am Inserting multiple records at same time, While Inserting record the AssetTag need to be generated automatically with asset prefix

after generating the asset tag for a item we need to increment lastUsedIndex of particular category in Category Table(Here keyboard) or we need to increment lastUsedIndex and concatenate with assetPrefix

For Example: For key Board AssetTag would be KYB00001, KYB00002.....

Example Json Data

declare @Asset_Info_Json nvarchar(max)
set @Asset_Info_Json = N'[{
"SerialNumber" : "S1",
"categoryid" : "1"
},{
"SerialNumber" : "S2",
"categoryid" : "1"
}]'
exec USP_TEST @Asset_Info_Json

Expected output (Id auto generated)

Id	AssetTag	SerialNumber	categoryid
1	KYB00001	S1	1
2	KYB00002	S2	1

Here is my stored procedure

Create procedure USP_TEST(@Asset_Info_Json varchar(max))
as
 begin
 begin transaction save_asset
 begin try


Insert into asset(AssetTag,SerialNumber,categoryid)
select  
 AssetTag = (select assetPrefix + cast(FORMAT(isnull(lastusedIndex,0) + 1,'0000#') as varchar) from category where id = CategoryId),
 SerialNumber,categoryid
From OPENJSON(@Asset_Info_Json)
WITH (SerialNumber varchar(30),categoryid varchar(30))



commit transaction save_asset
 end try
 begin catch
 rollback transaction save_asset
 end catch
end

Query for generate tag and update index column

update category set lastusedIndex = isnull(lastusedIndex,0) + 1 
OUTPUT Inserted.assetPrefix + cast(FORMAT(Inserted.lastusedIndex, '0000#') as varchar) as SerialNumber
where id = 1 

I tried table variable but it works for single item

We cannot use custom function to generate Asset Tag because We cannot use Update statement inside function

Answer 1

Multiple AssetTag values can be calculated using ROW_NUMBER.

You need to OUTPUT the AssetTag and categoryid into a table variable, then aggregate it back up to get the last id, in order to update the category table.

Because the AssetTag is a varchar column, it would be complex to have to parse the number back out. Instead you can use MERGE in order to OUTPUT columns which are not being inserted.

• You should also use locking hints on category to prevent concurrency issues.
• Do not messa round with CATCH... ROLLBACK instead just use SET XACT_ABORT ON which does it all for you.
• If the number of categories is small, consider changing this whole design to use SEQUENCE instead.

CREATE OR ALTER PROC USP_TEST
  @Asset_Info_Json nvarchar(max)
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

DECLARE @indexes TABLE (categoryid int, NewIndex int, PRIMARY KEY (categoryid, NewIndex));

WITH source AS (
    SELECT
      j.SerialNumber,
      j.categoryid,
      c.assetPrefix,
      NewIndex = ISNULL(c.lastusedIndex, 0) + ROW_NUMBER() OVER (PARTITION BY j.categoryid ORDER BY j.SerialNumber)
    FROM OPENJSON(@Asset_Info_Json)
      WITH (
        SerialNumber varchar(30),
        categoryid varchar(30)
      ) j
    JOIN category c WITH (HOLDLOCK, UPDLOCK) ON c.id = j.categoryId
)
MERGE asset a
USING source s
ON 1=0 --never match
WHEN NOT MATCHED THEN
  INSERT
    (AssetTag, SerialNumber, categoryid)
  VALUES
    (s.assetPrefix + FORMAT(s.NewIndex, '0000#'), s.SerialNumber, s.categoryid)
OUTPUT s.categoryid, s.NewIndex
INTO @indexes (categoryid, NewIndex)
;

UPDATE c
SET lastusedIndex = LastIndex
FROM category c
JOIN (
    SELECT
      i.categoryid,
      LastIndex = MAX(i.NewIndex)
    FROM @indexes i
    GROUP BY i.categoryid
) i ON i.categoryid = c.id;

COMMIT TRAN;