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

Charlieface · Accepted Answer · 2023-09-11 13:25:00Z

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;

answered Sep 11, 2023 at 13:25

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

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

2 Comments

Relax Over a year ago

Can we use Triggers for update lastusedIndex ?

Charlieface Over a year ago

In theory, but it's made more complicated by the fact the tag is a string rather than just the number.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related