SQL Server存储过程批量插入JSON数据时自动生成资产标签

核心思路 #

1. 解析输入的JSON数据到临时表，明确待插入的资产信息（序列号、分类ID）
2. 关联分类表获取每个分类的当前索引（初始为Null时默认设为0），通过分组序号生成每个资产的递增序号
3. 批量插入资产表并记录每个分类的最大新索引
4. 按分类汇总更新分类表的lastusedIndex

完整存储过程代码 #

CREATE PROCEDURE dbo.BulkInsertAssetsWithAutoTag
    @JsonData NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    -- 步骤1：解析JSON到临时表存储待插入资产
    CREATE TABLE #TempAssets (
        SerialNumber NVARCHAR(100),
        CategoryId INT
    );

    INSERT INTO #TempAssets (SerialNumber, CategoryId)
    SELECT SerialNumber, CategoryId
    FROM OPENJSON(@JsonData)
    WITH (
        SerialNumber NVARCHAR(100) '$.SerialNumber',
        CategoryId INT '$.CategoryId'
    );

    -- 步骤2：计算资产标签及分类新索引
    CREATE TABLE #UpdatedCategories (
        CategoryId INT,
        NewLastUsedIndex INT
    );

    WITH CategoryCurrentIndex AS (
        -- 加UPDLOCK+HOLDLOCK防止并发更新时索引重复
        SELECT 
            c.Id AS CategoryId,
            c.assetPrefix,
            ISNULL(c.lastusedIndex, 0) AS CurrentIndex
        FROM Category c WITH (UPDLOCK, HOLDLOCK)
        JOIN #TempAssets ta ON c.Id = ta.CategoryId
        GROUP BY c.Id, c.assetPrefix, c.lastusedIndex
    ),
    AssetWithTag AS (
        SELECT 
            ta.SerialNumber,
            ta.CategoryId,
            -- 拼接前缀+补零的5位序号，可根据需求调整位数
            cci.assetPrefix + RIGHT('00000' + CAST(cci.CurrentIndex + ROW_NUMBER() OVER(PARTITION BY ta.CategoryId ORDER BY (SELECT NULL)) AS NVARCHAR(5)), 5) AS AssetTag,
            cci.CurrentIndex + ROW_NUMBER() OVER(PARTITION BY ta.CategoryId ORDER BY (SELECT NULL)) AS NewIndex
        FROM #TempAssets ta
        JOIN CategoryCurrentIndex cci ON ta.CategoryId = cci.CategoryId
    )
    -- 步骤3：批量插入资产表，同时捕获每个分类的最大新索引
    INSERT INTO asset (AssetTag, SerialNumber, categoryid)
    OUTPUT inserted.categoryid, inserted.NewIndex INTO #UpdatedCategories(CategoryId, NewLastUsedIndex)
    SELECT AssetTag, SerialNumber, CategoryId
    FROM AssetWithTag;

    -- 步骤4：更新分类表的lastusedIndex为当前分类的最大新索引
    UPDATE c
    SET c.lastusedIndex = uc.NewLastUsedIndex
    FROM Category c
    JOIN (
        SELECT CategoryId, MAX(NewLastUsedIndex) AS NewLastUsedIndex
        FROM #UpdatedCategories
        GROUP BY CategoryId
    ) uc ON c.Id = uc.CategoryId;

    -- 清理临时表
    DROP TABLE #TempAssets;
    DROP TABLE #UpdatedCategories;

    COMMIT TRANSACTION;
END

关键说明 #

• 并发处理：在查询分类当前索引时使用WITH (UPDLOCK, HOLDLOCK)，锁定分类行避免多个会话同时插入时生成重复的资产标签
• 序号格式：示例中补零为5位，可根据需求调整RIGHT('00000' + ..., 5)中的数字
• 事务控制：使用SET XACT_ABORT ON和显式事务确保插入与更新操作的原子性，避免部分执行导致数据不一致

内容的提问来源于stack exchange，提问作者Relax