SQL Server批量插入数据的错误校验最优方案咨询

针对批量CSV数据导入+全量校验的需求，最优方案是在临时表层面完成批量校验，避免触发器的事务冲突问题，同时比循环校验高效数倍。核心逻辑：先把CSV导入临时表，一次性校验所有数据并收集错误，无错再写入最终表，有错则返回所有错误信息。

实现步骤 #

1. 扩展临时表（可选，或用独立错误表） #

给TempSchoolCOA新增错误信息列，用于存储每行的校验错误：

ALTER TABLE dbo.TempSchoolCOA
ADD ErrorMessage NVARCHAR(MAX);

2. 修改存储过程usp_LoadSchoolCOA #

重构存储过程，加入批量校验、错误收集、条件执行MERGE的逻辑：

ALTER PROCEDURE usp_LoadSchoolCOA
    @FullFilePath NVARCHAR(MAX) 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @ErrorCount INT;

    -- 清空临时表
    TRUNCATE TABLE dbo.TempSchoolCOA;

    -- 批量导入CSV到临时表
    SET @sql = N'BULK INSERT dbo.TempSchoolCOA FROM ''' + @FullFilePath + ''' 
                WITH (FORMAT=''CSV'', FIELDTERMINATOR='','', ROWTERMINATOR=''0x0a'', FIRSTROW=2);';
    EXEC sp_executesql @sql;

    -- ========== 批量校验逻辑 ==========
    UPDATE dbo.TempSchoolCOA
    SET ErrorMessage = STRING_AGG(ErrorItem, '; ')
    FROM (
        SELECT 
            NewOPEID, PriorOPEID, CodeCOA, DateCOA,
            ErrorItem = CASE
                WHEN NewOPEID NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'NewOPEID必须是8位数字'
                WHEN NOT EXISTS(SELECT 1 FROM dbo.SchoolDetails WHERE OPEID = NewOPEID) THEN 'NewOPEID不存在于SchoolDetails表: ' + NewOPEID
                WHEN PriorOPEID NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'PriorOPEID必须是8位数字'
                WHEN NOT EXISTS(SELECT 1 FROM dbo.SchoolDetails WHERE OPEID = PriorOPEID) THEN 'PriorOPEID不存在于SchoolDetails表: ' + PriorOPEID
                WHEN NOT EXISTS(SELECT 1 FROM dbo.CodeCOARef WHERE CodeCOA = CodeCOA) THEN 'CodeCOA不存在于CodeCOARef表'
                ELSE NULL
            END
        FROM dbo.TempSchoolCOA
    ) AS Validation
    WHERE TempSchoolCOA.NewOPEID = Validation.NewOPEID 
      AND TempSchoolCOA.PriorOPEID = Validation.PriorOPEID;

    -- 统计错误行数
    SELECT @ErrorCount = COUNT(*) 
    FROM dbo.TempSchoolCOA 
    WHERE ErrorMessage IS NOT NULL;

    -- ========== 根据校验结果执行逻辑 ==========
    IF @ErrorCount > 0
    BEGIN
        -- 返回所有错误信息
        PRINT '发现' + CAST(@ErrorCount AS VARCHAR) + '条数据错误，终止导入：';
        SELECT NewOPEID, PriorOPEID, CodeCOA, DateCOA, ErrorMessage 
        FROM dbo.TempSchoolCOA 
        WHERE ErrorMessage IS NOT NULL;
    END
    ELSE
    BEGIN
        -- 校验通过，执行MERGE并返回加载行数
        PRINT '数据校验通过，开始导入...';
        BEGIN TRANSACTION;
        BEGIN TRY
            MERGE INTO dbo.SchoolCOA AS TGT
            USING (SELECT NewOPEID, PriorOPEID, CodeCOA, DateCOA FROM dbo.TempSchoolCOA) AS SRC
                ON TGT.NewOPEID = SRC.NewOPEID AND TGT.PriorOPEID = SRC.PriorOPEID
            WHEN MATCHED THEN
                UPDATE SET
                    TGT.CodeCOA = SRC.CodeCOA,
                    TGT.DateCOA = SRC.DateCOA
            WHEN NOT MATCHED THEN
                INSERT (NewOPEID, PriorOPEID, CodeCOA, DateCOA)
                VALUES (SRC.NewOPEID, SRC.PriorOPEID, SRC.CodeCOA, SRC.DateCOA);
            
            -- 返回加载行数（插入+更新）
            PRINT '导入完成，共处理' + CAST(@@ROWCOUNT AS VARCHAR) + '行数据';
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            EXEC usp_GetErrorInfo;
            ROLLBACK TRANSACTION;
        END CATCH;
    END
END;

3. 清理冗余对象 #

删除之前的触发器trg_iu_SchoolCOA，因为不再需要：

DROP TRIGGER trg_iu_SchoolCOA;

方案优势 #

• 高效性：用批量UPDATE做校验，避免逐行循环，CPU开销极低
• 完整性：一次性收集所有错误，不会像触发器那样只报第一条错误就终止
• 安全性：校验不通过时完全不写入最终表，保证数据一致性
• 可扩展性：只需修改校验逻辑部分，即可快速适配另外2张表的需求

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