SQL Server INSERT 多列值語法：VALUES 列建構子與 UNION ALL 效能比較

現代標準語法：VALUES 列建構子 (SQL Server 2008+)

INSERT INTO dbo.Students (StudentID, Name, School)
VALUES 
    ('040501', '孫明', '山東大學'),
    ('040502', '李浩', '山東師範'),
    ('040503', '王剛', '煙台大學')

優點：單一陳述式、原子性、計畫快取友善、語法簡潔、最多 1000 列值。

傳統相容寫法：UNION ALL (SQL 2000/2005)

INSERT INTO dbo.Students (StudentID, Name, School)
SELECT '040501', '孫明', '山東大學' UNION ALL
SELECT '040502', '李浩', '山東師範' UNION ALL
SELECT '040503', '王剛', '煙台大學'

缺點：冗長、每列獨立 SELECT、舊版優化器可能產生多計畫。

大量資料 (>1000 列) 分批策略

-- C# 批次參數化範例
const string sql = @"
    INSERT INTO dbo.Students (StudentID, Name, School)
    VALUES (@id, @name, @school)";

using (var cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("@id", SqlDbType.VarChar, 20);
    cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50);
    cmd.Parameters.Add("@school", SqlDbType.NVarChar, 100);

    foreach (var batch in students.Chunk(1000))
    {
        // Table-Valued Parameter (TVP) 更高效
        // 或迴圈加參數執行
    }
}

Table-Valued Parameters (TVP) —— 企業級大量插入首選

-- 1. 建立使用者定義表類型
CREATE TYPE dbo.StudentList AS TABLE (
    StudentID VARCHAR(20),
    Name NVARCHAR(50),
    School NVARCHAR(100)
)

-- 2. 儲存程序接收 TVP
CREATE PROCEDURE dbo.BulkInsertStudents @Students dbo.StudentList READONLY
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO dbo.Students (StudentID, Name, School)
    SELECT StudentID, Name, School FROM @Students
END

-- 3. C# 呼叫
var dt = new DataTable();
dt.Columns.Add("StudentID", typeof(string));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("School", typeof(string));
// 填入資料...
using (var cmd = new SqlCommand("dbo.BulkInsertStudents", conn) { CommandType = CommandType.StoredProcedure })
{
    var param = cmd.Parameters.AddWithValue("@Students", dt);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.StudentList";
    cmd.ExecuteNonQuery();
}

優點：單次往返、伺服器端集合操作、型別安全、計畫重用、無 1000 列限制。

效能對照 (10,000 列測試環境)

SqlBulkCopy：極大量資料最終武器

using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "dbo.Students";
    bulk.BatchSize = 5000;
    bulk.BulkCopyTimeout = 300;
    bulk.ColumnMappings.Add("StudentID", "StudentID");
    bulk.ColumnMappings.Add("Name", "Name");
    bulk.ColumnMappings.Add("School", "School");
    bulk.WriteToServer(dataTable);  // 或 IDataReader
}

• 最小日誌 (簡單/大量記錄復原模式 + TABLOCK)
• 支援 IDataReader 流式讀取，記憶體常數
• 可對應欄位順序不同、名稱不同

最佳實踐決策樹

1. 資料 < 1000 列、臨時腳本 → VALUES (...), (...), ...
2. 資料 1000-10000 列、應用程式 → TVP 或分批 VALUES
3. 資料 > 10000 列、ETL/遷移 → SqlBulkCopy + TABLOCK + 簡單復原模式
4. 需觸發器/檢查約束逐列生效 → 僅能逐列或小批次
5. 舊版 SQL 2000/2005 → UNION ALL 分批

常見錯誤

• VALUES 超過 1000 列 → 錯誤 10738
• 字串拼接 SQL → 注入風險、計畫快取汙染
• 大量插入未停用非叢集索引 → 維護開銷大 (可考慮 DISABLE INDEX 後重建)
• 未指定欄位清單 → 結構描述變更時失效