SQL Server 存儲過程自動產生 INSERT 腳本：支援多資料型別、NULL 處理與逸出

需求：將某資料表的現有資料轉為 INSERT INTO ... VALUES (...) 腳本，便於跨環境同步資料 (無法用 Linked Server / Replication)。提供一個通用存儲過程 spGenInsertSQL，處理字串逸出、日期格式、二進位、NULL 等細節。

完整存儲過程碼

CREATE PROC dbo.spGenInsertSQL
    @tablename SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N'',
            @sqlValues NVARCHAR(MAX) = N'',
            @colList NVARCHAR(MAX) = N'';

    -- 1. 建立動態 SQL 片段：逐欄位產生 VALUES 運算式
    SELECT
        @colList += QUOTENAME(name) + N',',
        @sqlValues +=
            CASE
                -- 數值、位元、日期時間、唯一識別碼等：直接 CAST
                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：ISO 8601 格式
                WHEN xtype IN (58,61) THEN
                    N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE N'''''''''''' + CONVERT(NVARCHAR(23), ' + QUOTENAME(name) + N', 126) + N'''''''''''' END + N'','''
                -- char/varchar/text：單引號逸出 (雙倍)
                WHEN xtype IN (167,175) 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'','''
                -- nchar/nvarchar/ntext：前綴 N、單引號逸出
                WHEN xtype IN (231,239) 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'','''
                -- binary/varbinary/image：轉十六進位 0x...
                WHEN xtype IN (165,173) THEN
                    N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN N''NULL'' ELSE sys.fn_varbintohexstr(' + QUOTENAME(name) + N') END + N'','''
                ELSE N'N''NULL'' + N'','''
            END
    FROM syscolumns
    WHERE id = OBJECT_ID(@tablename)
    ORDER BY colorder;

    -- 2. 移除尾端逗號
    SET @colList = LEFT(@colList, LEN(@colList) - 1);
    SET @sqlValues = LEFT(@sqlValues, LEN(@sqlValues) - 5); -- 移除最後 + N',''

    -- 3. 組裝最終 SELECT 陳述式
    SET @sql = N'SELECT N''INSERT INTO ' + QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@tablename))) + N'.' + QUOTENAME(@tablename) + N' (' + @colList + N') VALUES ('' + ' + @sqlValues + N' + N'')'' FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID(@tablename))) + N'.' + QUOTENAME(@tablename);

    -- 4. 執行並輸出
    EXEC sp_executesql @sql;
END
GO

使用範例與輸出

EXEC dbo.spGenInsertSQL 'SysSample';

輸出 (每列一條 INSERT)：

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

關鍵處理細節

• 單引號逸出：REPLACE(val, '''', '''''') (字串型別)。
• Unicode 字串前綴 N：N'...' (nchar/nvarchar/ntext)。
• 日期時間統一 ISO 8601：CONVERT(..., 126) (含毫秒、無時區)，跨語言/地區安全。
• 二進位轉十六進位：sys.fn_varbintohexstr() 產生 0x... 格式。
• NULL 輸出為關鍵字 NULL (無引號)。
• 欄位名加方括號：QUOTENAME 防止保留字/空格衝突。

限制與改進方向

• 資料量極大 (百萬列) 時，輸出過大導致 SSMS 卡頓/截斷。改用 bcp / BCP OUT 導出原生格式或 INSERT ... SELECT 跨伺服器同步。
• 不支援 FILESTREAM、HierarchyId、Geometry/Geography 等 CLR 型別，需擴充 CASE 分支。
• 可加入 WHERE 參數篩選特定資料區間。