SQL Server 自動產生 INSERT 預存程序工具 sp_GenInsert 完整原始碼 (第二版)

本文提供 sp_GenInsert 完整可執行原始碼，自動依資料表結構產生標準化 INSERT 預存程序，含參數宣告、欄位清單、VALUES 子句，建立於 master 即可跨資料庫呼叫。

完整建立腳本

USE master;
GO
IF OBJECT_ID('sp_GenInsert', 'P') IS NOT NULL DROP PROCEDURE sp_GenInsert;
GO

CREATE PROCEDURE sp_GenInsert
    @TableName VARCHAR(130),
    @ProcedureName VARCHAR(130)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @maxcol INT, @TableID INT;
    SET @TableID = OBJECT_ID(@TableName);

    SELECT @maxcol = MAX(colorder) FROM syscolumns WHERE id = @TableID;

    -- 1. 程序宣告
    SELECT 'CREATE PROCEDURE ' + RTRIM(@ProcedureName) AS [text], 0 AS colorder INTO #TempProc
    UNION ALL
    -- 2. 參數宣告
    SELECT '@' + c.name + ' ' + t.name +
        CASE WHEN t.name IN ('binary','char','nchar','nvarchar','varbinary','varchar')
             THEN '(' + RTRIM(CONVERT(CHAR(4), c.length)) + ')'
             ELSE '' END +
        CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
        c.colorder
    FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
    WHERE c.id = @TableID AND t.name <> 'sysname'
    UNION ALL
    -- 3. AS 區塊
    SELECT 'AS', @maxcol + 1
    UNION ALL
    -- 4. INSERT INTO 陳述式
    SELECT 'INSERT INTO ' + @TableName, @maxcol + 2
    UNION ALL
    SELECT '(', @maxcol + 3
    UNION ALL
    -- 5. 欄位清單
    SELECT c.name + CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
        c.colorder + @maxcol + 3
    FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
    WHERE c.id = @TableID AND t.name <> 'sysname'
    UNION ALL
    -- 6. VALUES 子句
    SELECT ')', @maxcol * 2 + 4
    UNION ALL
    SELECT 'VALUES (', @maxcol * 2 + 5
    UNION ALL
    SELECT '@' + c.name + CASE WHEN c.colorder < @maxcol THEN ',' ELSE ' ' END,
        c.colorder + @maxcol * 2 + 5
    FROM syscolumns c JOIN systypes t ON c.xtype = t.xtype
    WHERE c.id = @TableID AND t.name <> 'sysname'
    UNION ALL
    SELECT ')', @maxcol * 3 + 6
    ORDER BY colorder;
END;
GO

使用範例

EXEC sp_GenInsert 'Employees', 'INS_Employees';

輸出結果特色

• 依 colorder 保持原始欄位順序
• 字串/二進位型別自動帶入長度（如 NVARCHAR(40)）
• 結果以單欄 text 逐行回傳，方便複製/導出
• 若表含 IDENTITY 欄位，產出碼會包含該欄位參數，執行前需手動加上 SET IDENTITY_INSERT ON 或移除該參數

進階擴充方向

1. 排除計算欄位/TimeStamp：加入 WHERE c.iscomputed = 0 AND t.name <> 'timestamp'
2. 自動處理 IDENTITY：偵測 COLUMNPROPERTY(id, name, 'IsIdentity') = 1 時自動產生 SET IDENTITY_INSERT ON/OFF
3. 輸出至檔案：INSERT #TempProc EXEC sp_GenInsert ...; BCP "SELECT text FROM #TempProc ORDER BY colorder" queryout "C:\Procs\INS_Employees.sql" -c -T
4. 支援 Schema：參數改為 @SchemaName, @TableName，查詢 OBJECT_ID(@SchemaName + '.' + @TableName)