SQL 自動生成 INSERT 腳本工具：動態構建含標識列、類型轉換、NULL 處理的數據遷移神器

核心功能

• 自動檢測標識列 → 生成 SET IDENTITY_INSERT ON/OFF。
• 根據數據類型智能加引號/轉義（字符/日期/唯一標識符 vs 數值/位元）。
• NULL 值輸出 NULL 關鍵字，非 NULL 正確格式化。
• 動態拼接列名、值列表，生成可直接執行的批量 INSERT 語句。

完整 T-SQL 實現

USE Test;
GO
DECLARE
  @SrcTable  SYSNAME = 'DutyHistory',
  @DstTable  SYSNAME = 'Duty',
  @SqlCols   NVARCHAR(MAX) = '',
  @SqlVals   NVARCHAR(MAX) = '',
  @SqlIdIns  NVARCHAR(MAX) = '';

-- 1. 標識列處理
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@SrcTable) AND is_identity = 1)
    SET @SqlIdIns = 'SET IDENTITY_INSERT ' + QUOTENAME(@DstTable) + ' ON;';

-- 2. 構建列名與值表達式
SELECT
  @SqlCols += ', ' + QUOTENAME(name),
  @SqlVals += ' + '' ,'' + CASE WHEN ' + QUOTENAME(name) + ' IS NULL THEN ''NULL'' ELSE ' +
    CASE WHEN system_type_id IN (167,175,231,239,40,41,42,43,58,61,98,99,106,108,122,165,173,189,241,256) -- 字符/日期/二進制/UNIQUEIDENTIFIER
         THEN '''''''' + REPLACE(' + QUOTENAME(name) + ', '''''''', '''''''''') + ''''''''
         ELSE 'CONVERT(NVARCHAR(MAX), ' + QUOTENAME(name) + ')'
    END + ' END'
FROM sys.columns
WHERE object_id = OBJECT_ID(@SrcTable);

SET @SqlCols = 'INSERT INTO ' + QUOTENAME(@DstTable) + ' (' + STUFF(@SqlCols, 1, 2, '') + ') ';
SET @SqlVals = 'SELECT ' + STUFF(@SqlVals, 1, 3, '') + ' FROM ' + QUOTENAME(@SrcTable);

-- 3. 輸出完整腳本
DECLARE @Final NVARCHAR(MAX) = 
  'IF OBJECT_ID(''' + @DstTable + ''',''U'') IS NULL RETURN;' + CHAR(13) +
  @SqlIdIns + CHAR(13) +
  @SqlCols + @SqlVals + CHAR(13) +
  CASE WHEN @SqlIdIns <> '' THEN 'SET IDENTITY_INSERT ' + QUOTENAME(@DstTable) + ' OFF;' ELSE '' END;

EXEC sp_executesql @Final;

輸出示例

IF OBJECT_ID('Duty','U') IS NULL RETURN;
SET IDENTITY_INSERT Duty ON;
INSERT INTO Duty ([id],[Name],[WorkDate],[WorkHours]) SELECT 1,'Robert','2008-01-01 00:00:00.000',3.4
INSERT INTO Duty ([id],[Name],[WorkDate],[WorkHours]) SELECT 2,'Robert','2008-01-02 00:00:00.000',3.4
...
SET IDENTITY_INSERT Duty OFF;

關鍵技術點解析

擴展建議

• 封裝為存儲過程 sp_GenerateInsertScript @SrcTable, @DstTable, @WhereClause。
• 支持 WHERE 過濾、TOP N 抽樣、ORDER BY 確定性。
• 大表分批生成，避免單批腳本過大導致 SSMS/命令超時。
• 集成至 CI/CD 流程，實現參考數據/種子數據版本化部署。