SQL Server 2008+ 自定義資料表類型 (Table-Valued Parameters) 實現批次插入/更新

SQL Server TVP (Table-Valued Parameters) 批次操作完整範例

SQL Server 2008 引入使用者定義資料表類型 (UDTT) 搭配資料表值參數 (TVP)，允許從應用層 (ADO.NET SqlParameter + DataTable) 將整張表作為參數傳遞給預存程序，實現高效批次 INSERT/UPDATE/MERGE，避免逐行往返網路開銷。

1. 資料庫端建立類型與預存程序

-- 建立基礎表
CREATE TABLE dbo.tab (
  tabvalue VARCHAR(10) NOT NULL
);

-- 建立使用者定義資料表類型 (UDTT)
CREATE TYPE dbo.Type_tab AS TABLE (
  Type_tabvalue VARCHAR(10) NOT NULL
);

-- 建立預存程序接收 TVP (必須加 READONLY)
CREATE PROCEDURE dbo.p_Tab
  @ptab dbo.Type_tab READONLY
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.tab (tabvalue)
  SELECT Type_tabvalue FROM @ptab;
END;
GO

2. T-SQL 測試呼叫

-- 方式 1：直接 INSERT ... SELECT
DECLARE @tt dbo.Type_tab;
INSERT INTO @tt VALUES ('a'), ('b'), ('c');
INSERT INTO dbo.tab SELECT Type_tabvalue FROM @tt;

-- 方式 2：呼叫預存程序
DECLARE @tt2 dbo.Type_tab;
INSERT INTO @tt2 VALUES ('a'), ('b'), ('c');
EXEC dbo.p_Tab @tt2;

3. C# (ADO.NET) 呼叫範例

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("dbo.p_Tab", conn) { CommandType = CommandType.StoredProcedure })
{
    // 建立 DataTable 對應 UDTT 結構
    var dt = new DataTable();
    dt.Columns.Add("Type_tabvalue", typeof(string));

    foreach (var entity in entityList)  // entityList 為業務實體集合
    {
        var row = dt.NewRow();
        row["Type_tabvalue"] = entity.TabValue;
        dt.Rows.Add(row);
    }

    // 關鍵：SqlDbType.Structured + TypeName=UDTT 名稱
    var param = cmd.Parameters.Add("@ptab", SqlDbType.Structured);
    param.TypeName = "dbo.Type_tab";  // 必須指定完整類型名
    param.Value = dt;

    conn.Open();
    return cmd.ExecuteNonQuery();  // 返回影響總行數
}

4. 進階：MERGE UPSERT 模式 (單次往返完成新增/更新)

CREATE PROCEDURE dbo.p_Tab_Upsert
  @ptab dbo.Type_tab READONLY
AS
BEGIN
  SET NOCOUNT ON;
  MERGE INTO dbo.tab AS target
  USING @ptab AS source
  ON target.tabvalue = source.Type_tabvalue
  WHEN MATCHED THEN
    UPDATE SET target.tabvalue = source.Type_tabvalue  -- 示例更新其他欄位
  WHEN NOT MATCHED THEN
    INSERT (tabvalue) VALUES (source.Type_tabvalue);
END;

5. 效能優勢與適用場景

6. 注意事項

• TVP 參數必須宣告 READONLY，預存程序內不可修改參數內容。
• SqlDbType.Structured + TypeName 兩者缺一不可。
• UDTT 結構變更需同步更新應用端 DataTable 結構，否則執行時報錯。
• 大量資料 (> 1 萬行) 建議改用 SqlBulkCopy 或 bcp 工具。
• TVP 不支援跨資料庫/伺服器傳遞。