SQL Server 產生資料發布 INSERT 腳本預存程序

需求背景

SSMS「產生指令碼」功能可發布架構，但無法直接發布資料。需要將指定資料表的資料轉為 INSERT INTO ... VALUES (...) 陳述式，支援欄位選取、篩選條件。

預存程式碼

CREATE PROCEDURE [TOOL].[Script_Insert]
    @Schema   sysname = 'dbo',
    @Table    sysname,
    @Filter   sysname = NULL,
    @Field0   sysname,
    @Field1   sysname = NULL,
    @Field2   sysname = NULL,
    @Field3   sysname = NULL,
    @Field4   sysname = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @NL VARCHAR(2) = CHAR(13) + CHAR(10);
    DECLARE @vSql NVARCHAR(MAX), @objName sysname, @fieldList sysname;
    
    -- 1. 正規化結構描述名稱
    IF @Schema = '' SET @Schema = 'dbo';
    SET @objName = @Schema + '.' + @Table;
    
    -- 2. 動態建構 SELECT 陳述式，輸出 INSERT 字串
    SET @vSql = 'SET QUOTED_IDENTIFIER OFF; SELECT "INSERT INTO ' + @objName + '(' + @Field0;
    
    IF @Field1 IS NOT NULL SET @vSql = @vSql + ' ,' + @Field1;
    IF @Field2 IS NOT NULL SET @vSql = @vSql + ' ,' + @Field2;
    IF @Field3 IS NOT NULL SET @vSql = @vSql + ' ,' + @Field3;
    IF @Field4 IS NOT NULL SET @vSql = @vSql + ' ,' + @Field4;
    
    SET @vSql = @vSql + ' )' + @NL + 'VALUES(" + QUOTENAME(' + @Field0 + ', CHAR(39))';
    
    IF @Field1 IS NOT NULL SET @vSql = @vSql + @NL + ' +", " + QUOTENAME(ISNULL(' + @Field1 + ', ""), CHAR(39))';
    IF @Field2 IS NOT NULL SET @vSql = @vSql + @NL + ' +", " + QUOTENAME(ISNULL(' + @Field2 + ', ""), CHAR(39))';
    IF @Field3 IS NOT NULL SET @vSql = @vSql + @NL + ' +", " + QUOTENAME(ISNULL(' + @Field3 + ', ""), CHAR(39))';
    IF @Field4 IS NOT NULL SET @vSql = @vSql + @NL + ' +", " + QUOTENAME(ISNULL(' + @Field4 + ', ""), CHAR(39))';
    
    SET @vSql = @vSql + @NL + ' +")"' + @NL + 'FROM ' + @objName;
    
    IF @Filter IS NOT NULL SET @vSql = @vSql + @NL + 'WHERE ' + @Filter;
    
    -- 3. 列印並執行
    PRINT @vSql;
    EXEC(@vSql);
END

使用範例

-- 範例 1：設定表資料發布
EXEC [TOOL].[Script_Insert] 
    @Schema = 'COMMON', 
    @Table = 'Config', 
    @Filter = '[Key] LIKE ''test%''', 
    @Field0 = '[Key]', 
    @Field1 = 'SubKey', 
    @Field2 = 'Value', 
    @Field3 = NULL, 
    @Field4 = NULL;

-- 範例 2：主資料發布
EXEC [TOOL].[Script_Insert] 
    @Schema = 'COMMON', 
    @Table = 'MasterData', 
    @Filter = 'Category = ''SheetItem_PreloadType''', 
    @Field0 = 'Category', 
    @Field1 = 'Code', 
    @Field2 = 'Name', 
    @Field3 = 'Ord', 
    @Field4 = NULL;

輸出範例

INSERT INTO COMMON.Config([Key], SubKey, Value)
VALUES('test001', 'Sub1', 'Value1')
INSERT INTO COMMON.Config([Key], SubKey, Value)
VALUES('test002', 'Sub2', 'Value2')

設計亮點

• 動態 SQL 建構：支援 1-5 個欄位彈性組合
• QUOTENAME + CHAR(39)：正確處理單引號逸出、NULL 值轉空字串
• SET QUOTED_IDENTIFIER OFF：允許雙引號字串常數
• PRINT + EXEC：可先審查產生的 SQL 再決定是否執行
• 參數化設計：Schema、Table、Filter、Fields 全參數化

擴充建議

1. 支援更多欄位 (改用表值參數或 XML 傳遞欄位清單)
2. 加入 IDENTITY_INSERT 控制
3. 支援 MERGE 語法 (UPSERT 發布)
4. 輸出至檔案 (使用 bcp 或 PowerShell)