SQL Server 批次插入多筆資料：字串分割法

SQL Server 不支援陣列參數，若需一次插入 N 筆資料且不在應用程式端控制迴圈，常見做法是將 ID 陣列格式化為逗號分隔字串（如 '1,2,3,4,5'），傳入預存程序再以 CHARINDEX 與 SUBSTRING 分割逐筆插入。

預存程序範例

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
  @ProductId_Array VARCHAR(800),
  @ModuleId INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @PointerPrev INT = 1, @PointerCurr INT, @TId INT;
  BEGIN TRANSACTION;
  DELETE FROM ProductListSpecial WHERE ModuleId = @ModuleId;

  -- 處理第一個 ID
  SET @PointerCurr = CHARINDEX(',', @ProductId_Array, @PointerPrev);
  IF @PointerCurr = 0 SET @PointerCurr = LEN(@ProductId_Array) + 1;
  SET @TId = CAST(SUBSTRING(@ProductId_Array, @PointerPrev, @PointerCurr - @PointerPrev) AS INT);
  INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @TId);
  SET @PointerPrev = @PointerCurr + 1;

  -- 迴圈處理剩餘 ID
  WHILE @PointerPrev <= LEN(@ProductId_Array)
  BEGIN
    SET @PointerCurr = CHARINDEX(',', @ProductId_Array, @PointerPrev);
    IF @PointerCurr = 0 SET @PointerCurr = LEN(@ProductId_Array) + 1;
    SET @TId = CAST(SUBSTRING(@ProductId_Array, @PointerPrev, @PointerCurr - @PointerPrev) AS INT);
    INSERT INTO ProductListSpecial (ModuleId, ProductId) VALUES (@ModuleId, @TId);
    SET @PointerPrev = @PointerCurr + 1;
  END

  IF @@ERROR = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION;
  SET NOCOUNT OFF;
END

現代替代方案（SQL Server 2008+）

• Table-Valued Parameters (TVP)：定義使用者定義資料表型別，直接傳遞資料表變數，效能與可讀性更佳。
• OPENJSON (SQL 2016+)：傳入 JSON 陣列，以 OPENJSON 解析插入。
• STRING_SPLIT (SQL 2016+)：內建分割函數簡化邏輯。

若環境允許，強烈建議改用 TVP 或 JSON 取代字串分割，減少錯誤並提升維護性。