SQL Server 產生 INSERT 腳本工具：三種存儲過程實作比較

需求背景

SQL Server 內建「產生指令碼」精靈可產生 DDL，但無法直接產生資料的 INSERT 陳述式。本文提供三種不同風格的存儲過程，皆能依資料表自動產生包含所有資料的 INSERT 腳本，支援身分識別欄位、各類資料型別、NULL 處理與特殊字元跳脫。

方案一：標準游標法（OutputData）

特點：邏輯清晰、逐欄位動態建構 VALUES 子句、明確處理每種資料型別格式化。

CREATE PROCEDURE dbo.OutputData @tablename sysname
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @objectId INT = OBJECT_ID(@tablename);
  IF @objectId IS NULL BEGIN PRINT @tablename + N' 對象不存在'; RETURN; END
  IF OBJECTPROPERTY(@objectId, 'IsTable') <> 1 BEGIN PRINT @tablename + N' 不是表'; RETURN; END

  -- 檢查 IDENTITY 欄位
  DECLARE @ident INT = (SELECT status & 0x80 FROM syscolumns WHERE id = @objectId AND status & 0x80 = 0x80);
  IF @ident IS NOT NULL PRINT 'SET IDENTITY_INSERT ' + @tablename + ' ON';

  -- 動態建構欄位清單與 VALUES 範本
  DECLARE @column NVARCHAR(MAX) = N'', @columndata NVARCHAR(MAX) = N'';
  DECLARE @name SYSNAME, @xtype TINYINT;
  DECLARE cur CURSOR FOR SELECT name, xtype FROM syscolumns WHERE id = @objectId ORDER BY colid;
  OPEN cur;
  FETCH NEXT FROM cur INTO @name, @xtype;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @xtype NOT IN (189,34,35,99,98) -- 排除 timestamp, image, text, ntext, sql_variant
    BEGIN
      SET @column += CASE WHEN LEN(@column)=0 THEN N'' ELSE N',' END + QUOTENAME(@name);
      SET @columndata += CASE WHEN LEN(@columndata)=0 THEN N'' ELSE N','''','''' END +
        CASE @xtype
          WHEN 167 THEN N'''''''''+' + @name + N'+''''''''' -- varchar
          WHEN 175 THEN N'''''''''+' + @name + N'+''''''''' -- char
          WHEN 231 THEN N''''N'''''''+' + @name + N'+''''''''' -- nvarchar
          WHEN 239 THEN N''''N'''''''+' + @name + N'+''''''''' -- nchar
          WHEN 61  THEN N'''''''''+CONVERT(CHAR(23),' + @name + N',121)+''''''''' -- datetime
          WHEN 58  THEN N'''''''''+CONVERT(CHAR(16),' + @name + N',120)+''''''''' -- smalldatetime
          WHEN 36  THEN N'''''''''+CONVERT(CHAR(36),' + @name + N')+''''''''' -- uniqueidentifier
          ELSE @name
        END;
    END
    FETCH NEXT FROM cur INTO @name, @xtype;
  END
  CLOSE cur; DEALLOCATE cur;

  -- 產生並執行動態 SQL，輸出 INSERT 陳述式
  DECLARE @sql NVARCHAR(MAX) = N'SET NOCOUNT ON SELECT ''INSERT ' + @tablename + N'(' + @column + N') VALUES('''' AS ''--'', ' + @columndata + N', '')'' FROM ' + @tablename;
  PRINT '--' + @sql;
  EXEC (@sql);

  IF @ident IS NOT NULL PRINT 'SET IDENTITY_INSERT ' + @tablename + ' OFF';
END;
GO

方案二：單一動態 SQL 字串組裝法

特點：在單一 SELECT 中完成所有型別轉換邏輯，利用 CASE WHEN 產生每欄位的字串表示式，適合簡潔部署。

CREATE PROC proc_insert @tablename VARCHAR(256)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sqlstr NVARCHAR(MAX), @sqlstr1 NVARCHAR(MAX), @sqlstr2 NVARCHAR(MAX);
  SELECT @sqlstr = N'SELECT ''INSERT ' + @tablename + N'(';
  SELECT @sqlstr1 = N'', @sqlstr2 = N'(';
  SELECT @sqlstr1 = @sqlstr1 + N' VALUES (''''',
         @sqlstr2 = @sqlstr2 + name + N','
  FROM (
    SELECT
      CASE
        WHEN a.xtype = 173 THEN N'CASE WHEN ' + a.name + N' IS NULL THEN ''NULL'' ELSE CONVERT(VARCHAR(' + CAST(a.length*2+2 AS VARCHAR) + N'),' + a.name + N') END'
        WHEN a.xtype = 104 THEN N'CASE WHEN ' + a.name + N' IS NULL THEN ''NULL'' ELSE CONVERT(VARCHAR(1),' + a.name + N') END'
        WHEN a.xtype IN (175,167) THEN N'CASE WHEN ' + a.name + N' IS NULL THEN ''NULL'' ELSE '''''''''+REPLACE(' + a.name + N','''''''''','''''''''''')+''''''''' END'
        WHEN a.xtype = 61  THEN N'CASE WHEN ' + a.name + N' IS NULL THEN ''NULL'' ELSE '''''''''+CONVERT(VARCHAR(23),' + a.name + N',121)+''''''''' END'
        -- ... 其他型別同理
        ELSE N'''NULL'''
      END AS col, a.colid, a.name
    FROM syscolumns a
    WHERE a.id = OBJECT_ID(@tablename) AND a.xtype NOT IN (189,34,35,36)
  ) t ORDER BY colid;

  SELECT @sqlstr = @sqlstr + LEFT(@sqlstr2, LEN(@sqlstr2)-1) + N') ' + LEFT(@sqlstr1, LEN(@sqlstr1)-3) + N')'' FROM ' + @tablename;
  EXEC (@sqlstr);
  SET NOCOUNT OFF;
END;
GO

方案三：極簡 WHILE 迴圈法

特點：程式碼最短，直接在迴圈中拼接整條 INSERT 陳述式，適合臨時使用。

CREATE PROC spgeninsertsql @tablename VARCHAR(100)
AS
DECLARE @sql NVARCHAR(8000) = N'SELECT ''INSERT INTO ' + @tablename + N' VALUES(';
DECLARE @f1 VARCHAR(100), @f2 INT;
DECLARE xcursor CURSOR FOR SELECT name, xusertype FROM syscolumns WHERE id = OBJECT_ID(@tablename);
OPEN xcursor;
FETCH NEXT FROM xcursor INTO @f1, @f2;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = @sql +
    CASE WHEN @f2 IN (35,58,99,167,175,231,239,61) THEN
      CASE WHEN @f1 + N' IS NULL' THEN N'NULL' ELSE N'''''' + REPLACE(ISNULL(CAST(@f1 AS VARCHAR), N''), N'''', N'''''') + N'''''' END
    ELSE
      CASE WHEN @f1 + N' IS NULL' THEN N'NULL' ELSE CAST(@f1 AS VARCHAR) END
    END + N',';
  FETCH NEXT FROM xcursor INTO @f1, @f2;
END
CLOSE xcursor; DEALLOCATE xcursor;
SET @sql = LEFT(@sql, LEN(@sql)-1) + N')'' FROM ' + @tablename;
PRINT @sql;
EXEC (@sql);

三種方案比較

使用方式

EXEC dbo.OutputData 'MyTable';        -- 方案一
EXEC proc_insert 'MyTable';           -- 方案二
EXEC spgeninsertsql 'MyTable';        -- 方案三

輸出結果可直接複製到查詢視窗執行，或導出 `.sql` 檔供其他環境匯入。

擴充建議

• 加入 `WHERE` 子句參數，支援條件式匯出（如依日期範圍）。
• 支援 `SET IDENTITY_INSERT` 自動開關（方案一已內建）。
• 輸出檔案而非列印訊息：改用 `xp_cmdshell` 或 CLR 寫入檔案。
• 處理 `MAX` 型別、`FILESTREAM`、`HIERARCHYID` 等新型別。