SQL存储过程结合SCOPE_IDENTITY与SPLIT_STRING实现主从表插入

核心逻辑说明 #

• 传入的多科目成绩参数以|作为分隔符拼接
• 优先插入学生主表数据，使用SCOPE_IDENTITY()获取本次插入生成的自增主键ID
• 拆分成绩字符串后，将单条成绩与获取到的学生ID关联，批量写入成绩表

入参示例 #

@StudentName = 'John'
@score = 'Science-90|Biology-100|Math-90'

涉及表结构 #

学生主表 Student_Main #

学生成绩表 Student_Score #

完整存储过程代码 #

你已完成主表插入、自增ID获取的逻辑，补充STRING_SPLIT拆分批量插入部分即可。注意你之前写的SPLIT_STRING是拼写错误，SQL Server原生拆分函数名为STRING_SPLIT（2016及以上版本支持）。

CREATE PROCEDURE [dhub_PushData]    
    @studentName varchar(50),   
    @score varchar(MAX) -- 原定义varchar(50)长度过短，成绩条目多时会截断，建议调大
AS 
BEGIN 
    SET NOCOUNT ON;
    DECLARE @id bigint;

    -- 开启事务保证两表数据一致性
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 插入学生主表
        INSERT INTO Student_Main (studentName) 
        VALUES (@studentName);

        -- 获取刚插入的学生自增ID
        SELECT @id = SCOPE_IDENTITY();

        -- 拆分成绩字符串，批量插入成绩表
        INSERT INTO Student_Score (StudentID, Score)
        SELECT @id, TRIM(value)
        FROM STRING_SPLIT(@score, '|')
        WHERE TRIM(value) <> ''; -- 过滤空值，兼容首尾/中间多余分隔符的场景

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 异常时回滚事务，避免脏数据
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

补充说明 #

• 如果使用SQL Server 2016以下版本，没有原生STRING_SPLIT函数，需要自行实现字符串拆分表值函数替换即可
• 批量插入直接用INSERT ... SELECT的方式，不需要游标循环，性能更高
• 增加异常回滚逻辑，避免出现主表插入成功、成绩表插入失败的不一致数据

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