您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

依資料表結構自動產生 INSERT 陳述式：動態 SQL 預存程序實作

編輯：關於SqlServer

需求場景

資料遷移腳本產生：將現有資料轉為可攜帶的 INSERT 陳述式。
測試資料備份/還原：快速複製特定表資料至開發/測試環境。
版本控管資料種子：將基準資料以 SQL 檔納入 Git。

核心預存程序：sp_GenInsertSQL


CREATE PROCEDURE dbo.sp_GenInsertSQL
    @TableName sysname,          -- 目標表名 (含 schema 建議 dbo.Table)
    @WhereClause nvarchar(4000) = NULL,  -- 選擇性篩選條件 (不含 WHERE 關鍵字)
    @TopN int = NULL             -- 限制筆數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max) = N'';
    DECLARE @Cols nvarchar(max) = N'';
    DECLARE @Vals nvarchar(max) = N'';
    DECLARE @Sch  sysname = PARSENAME(@TableName, 2);
    DECLARE @Obj  sysname = PARSENAME(@TableName, 1);
    SET @Sch = ISNULL(@Sch, SCHEMA_NAME());

    -- 1. 取得欄位清單 (排除 identity、computed、timestamp、rowversion)
    SELECT @Cols = STRING_AGG(QUOTENAME(c.name), N', '),
           @Vals = STRING_AGG(
               CASE
                   WHEN t.name IN ('varchar','nvarchar','char','nchar','text','ntext','xml','sysname') THEN
                       N'''''''' + REPLACE(CAST(' + QUOTENAME(c.name) + N' AS nvarchar(max)), '''''''', '''''''''''') + N'''''''
                   WHEN t.name IN ('datetime','smalldatetime','date','time','datetime2','datetimeoffset') THEN
                       N'''''''' + CONVERT(nvarchar(30), ' + QUOTENAME(c.name) + N', 126) + N'''''''
                   WHEN t.name IN ('binary','varbinary','image','timestamp','rowversion') THEN
                       N'0x' + MASTER.dbo.fn_varbintohexstr(' + QUOTENAME(c.name) + N')
                   WHEN t.name IN ('uniqueidentifier') THEN
                       N'''''''' + CAST(' + QUOTENAME(c.name) + N' AS nvarchar(36)) + N'''''''
                   WHEN t.name IN ('bit') THEN
                       N'CASE WHEN ' + QUOTENAME(c.name) + N' = 1 THEN 1 ELSE 0 END'
                   ELSE
                       CAST(' + QUOTENAME(c.name) + N' AS nvarchar(max))
               END, N', ')
    FROM sys.columns c
    JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE c.object_id = OBJECT_ID(QUOTENAME(@Sch) + N'.' + QUOTENAME(@Obj))
      AND c.is_identity = 0
      AND c.is_computed = 0
      AND t.name NOT IN ('timestamp','rowversion')
    ORDER BY c.column_id;

    IF @Cols = N'' BEGIN RAISERROR(N'Table not found or no eligible columns: %s', 16, 1, @TableName); RETURN; END

    -- 2. 組裝動態查詢
    SET @SQL = N'SELECT N''INSERT INTO ' + QUOTENAME(@Sch) + N'.' + QUOTENAME(@Obj) + N' (' + @Cols + N') VALUES ('' + '
             + @Vals + N' + N'')'' AS InsertStmt'
             + N' FROM ' + QUOTENAME(@Sch) + N'.' + QUOTENAME(@Obj);
    IF @WhereClause IS NOT NULL SET @SQL += N' WHERE ' + @WhereClause;
    IF @TopN IS NOT NULL SET @SQL = N'SELECT TOP (' + CAST(@TopN AS nvarchar(10)) + N') * FROM (' + @SQL + N') t';

    -- 3. 執行並回傳結果集 (每列一個 INSERT 陳述式)
    EXEC sp_executesql @SQL;
END

使用範例


-- 產生全部資料
EXEC dbo.sp_GenInsertSQL N'dbo.Orders';

-- 產生特定條件
EXEC dbo.sp_GenInsertSQL N'dbo.Orders', N'OrderDate >= ''2023-01-01'' AND ShipCountry = ''USA''';

-- 產生前 100 筆
EXEC dbo.sp_GenInsertSQL N'dbo.OrderDetails', NULL, 100;

-- 結果集範例
-- INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], ...) VALUES ('ALFKI', 5, '2023-01-15T00:00:00', ...)
-- INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], ...) VALUES ('ANATR', 6, '2023-01-16T00:00:00', ...)

進階擴充建議

功能	實作方向
支援跨資料庫/連結伺服器	參數加入 `@ServerName`, `@DbName`，動態組裝四部份名稱。
批次輸出至檔案	搭配 `bcp "EXEC dbo.sp_GenInsertSQL ..." queryout "C:\Scripts\SeedData.sql" -c -T`。
處理 Identity 插入	選擇性輸出 `SET IDENTITY_INSERT ... ON; ... OFF;` 區塊。
大型資料分批	加入 `@BatchSize`，游標分頁產生，避免單一結果集過大。
資料遮罩敏感欄位	參數 `@MaskColumns`，產生時自動替換為預設/雜湊值。

替代方案比較

SSMS 產生腳本**：右鍵資料庫 → 任務 → 產生腳本 → 選擇「資料類型: 只資料」。適合一次性、互動式操作。
bcp 原生格式**：bcp Db.dbo.Table out "data.bcp" -N -T，效能最佳、二進位、跨版本相容，但非人類可讀。
第三方工具**：Redgate SQL Data Compare, ApexSQL Script, dbForge Data Generator — 功能豐富、GUI 友善、付費。