SQL Server 動態產生 INSERT 腳本預存程序：依資料表名稱自動生成資料遷移 SQL

應用場景：相同結構不同環境的資料同步

開發/測試/正式環境結構一致，需將特定資料表的資料以 INSERT 陳述式匯出，供他庫執行。無法使用 INSERT ... SELECT 跨資料庫（如離線、異構、版本不支援），需產生可攜帶的純 SQL 腳本。

核心邏輯：查詢系統目錄動態組裝 INSERT 陳述式

• 讀取 syscolumns（或 INFORMATION_SCHEMA.COLUMNS）取得欄位名稱、型別、長度。
• 依資料型別分類產生對應的字串化邏輯：
  🔢 數值型 → CAST(col AS VARCHAR)
  📅 日期時間 → CONVERT(CHAR(23), col, 121) (ISO 8601)
  🔤 字串型 → REPLACE(col, '''', '''''') 雙引號跳脫 + 單引號包裹
  🅱 二進位/大型物件 → 回傳 NULL 或略過（腳本說明不支援）
• 組合成 INSERT INTO [Table] ([Col1],[Col2]...) VALUES ('Val1','Val2'...) 格式。
• 輸出為結果集，每列一條完整 INSERT 陳述式。

完整預存程序碼

CREATE PROCEDURE [dbo].[spGenInsertSQL]
    @tablename SYSNAME  -- 使用 SYSNAME 內建型別 (NVARCHAR(128))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlCols     NVARCHAR(MAX) = N'';
    DECLARE @sqlValues   NVARCHAR(MAX) = N'';
    DECLARE @finalSql    NVARCHAR(MAX);

    -- 動態建構欄位清單與 VALUES 運算式
    SELECT 
        @sqlCols   += QUOTENAME(name) + N',',
        @sqlValues += CASE
            -- 數值型別 (tinyint, smallint, int, bigint, bit, decimal, numeric, money, float, real)
            WHEN xtype IN (48,52,56,59,60,62,104,106,108,122,127) THEN
                N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE CAST(' + QUOTENAME(name) + N' AS NVARCHAR(MAX)) END + N'','''
            -- 日期時間型別 (datetime, smalldatetime, date, time, datetime2, datetimeoffset)
            WHEN xtype IN (58,61,40,41,42,43) THEN
                N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE N'''''''' + CONVERT(NVARCHAR(30), ' + QUOTENAME(name) + N', 126) + N'''''''' END + N'','''
            -- 一般字串 (varchar, char, text)
            WHEN xtype IN (167,175,35) THEN
                N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE N'''''''' + REPLACE(CAST(' + QUOTENAME(name) + N' AS NVARCHAR(MAX)), N'''''''', N'''''''''''') + N'''''''' END + N'','''
            -- Unicode 字串 (nvarchar, nchar, ntext)
            WHEN xtype IN (231,239,99) THEN
                N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE N''N'''''''' + REPLACE(CAST(' + QUOTENAME(name) + N' AS NVARCHAR(MAX)), N'''''''', N'''''''''''') + N'''''''' END + N'','''
            -- 二進位/圖片/其他 → 略過或回傳 NULL
            ELSE N'N''NULL'' + N'','''
        END
    FROM syscolumns
    WHERE id = OBJECT_ID(@tablename)
      AND xtype NOT IN (34,35,99,241)  -- 略過 image, text, ntext, xml, timestamp 等大型/複雜型別
    ORDER BY colid;

    -- 移除末尾逗號
    SET @sqlCols   = LEFT(@sqlCols,   LEN(@sqlCols)   - 1);
    SET @sqlValues = LEFT(@sqlValues, LEN(@sqlValues) - 1);

    -- 組裝最終動態 SQL：對目標資料表每列產生一條 INSERT
    SET @finalSql = N'
        SELECT N''INSERT INTO '' + QUOTENAME(OBJECT_SCHEMA_NAME(' + CAST(OBJECT_ID(@tablename) AS NVARCHAR) + N')) + N''.'' + QUOTENAME(OBJECT_NAME(' + CAST(OBJECT_ID(@tablename) AS NVARCHAR) + N')) + N'' ('' + @cols + N'') VALUES ('' + '' + @vals + N'')''
        FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@tablename))) + N'.' + QUOTENAME(OBJECT_NAME(OBJECT_ID(@tablename)));

    -- 使用 sp_executesql 參數化傳入動態組裝的欄位/值運算式
    EXEC sp_executesql 
        @finalSql,
        N'@cols NVARCHAR(MAX), @vals NVARCHAR(MAX)',
        @cols = @sqlCols,
        @vals = @sqlValues;
END
GO

使用範例與輸出結果

EXEC dbo.spGenInsertSQL 'SysSample';

輸出範例（每列一筆 INSERT）：

INSERT INTO [dbo].[SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) VALUES ('0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C','任務調度系統',18,'2013-01-02T21:42:30.013','',NULL,'2013-01-02T21:42:30.013')
INSERT INTO [dbo].[SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) VALUES ('0004A6F3-EC28-4D1F-BA40-0FC4B2218C92','任務調度系統',18,'2013-07-09T19:36:00.060','',NULL,'2013-07-09T19:36:00.060')
...

優化與限制說明

進階替代方案

• SSMS 產生指令碼：右鍵資料庫 → 工作 → 產生指令碼 → 進階選項 → 「資料類型」選「Schema and data」或「Data only」，支援大量資料、自動處理 Identity、編碼、約束。
• bcp 命令列：bcp "SELECT * FROM dbo.Table" queryout data.bcp -S . -T -N（原生格式，高速、二進位安全）。
• Azure Data Studio / Data Factory：雲端遷移首選。

結語

spGenInsertSQL 以動態 SQL 實現「依表名產生 INSERT 腳本」，適合**小量資料、離線遷移、版本對照、手工部署**場景。掌握「系統目錄查詢 + 型別分派字串化 + 安全跳脫」三步驟，即可依需求擴充支援更多型別、加入 WHERE 過濾、輸出至檔案等功能。大量資料請改用專業 ETL 工具。