依據資料表結構自動產生 INSERT INTO 指令碼 (T-SQL 動態 SQL 實作)

開發/測試/遷移資料時，常需為指定鍵值的資料列快速產生 INSERT INTO ... VALUES (...) 指令碼。本文提供一支完整 T-SQL 腳本，輸入 資料表名、主鍵欄位名、主鍵值，自動讀取系統目錄查詢欄位清單與資料型別，動態組裝欄位列與對應值 (字串型別加引號、數值型別直寫、NULL 處理)，輸出可直接執行的 INSERT 指令。

核心邏輯

1. 查詢 sys.columns + systypes 取得欄位名、資料型別、是否主鍵。
2. 游標迴圈組裝 INSERT INTO Table (col1, col2, ...) VALUES (val1, val2, ...)。
3. 第二個游標逐欄位 sp_executesql 動態查詢實際值，依型別格式化：

• varchar/nvarchar → 'value'
• 數值型別 → 直寫 (NULL 轉 0)

完整腳本 (可直接執行)

/* 功能：依表結構產生 INSERT 指令
   參數：@strtablename (表名)、@strKeyName (PK名)、@strKeyValue (PK值) */
DECLARE @strtablename VARCHAR(50) = 'DebtApplyForm',
        @strKeyName   VARCHAR(50) = 'ApplyFormId',
        @strKeyValue  VARCHAR(50) = 'A200979469A1';

-- 1. 取得欄位清單 (含型別、主鍵標記) 存入暫存表 #temp
CREATE TABLE #temp (ColName VARCHAR(50), ColType VARCHAR(50));
INSERT INTO #temp
SELECT c.name, systypes.name
FROM sys.columns c
JOIN systypes ON c.system_type_id = systypes.xusertype
JOIN sys.objects o ON c.object_id = o.object_id
LEFT JOIN (
    SELECT ic.column_id
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1 AND i.object_id = OBJECT_ID(@strtablename)
) pk ON c.column_id = pk.column_id
WHERE o.name = @strtablename;

-- 2. 組裝欄位列
DECLARE @strSQL VARCHAR(5000) = 'INSERT INTO ' + @strtablename + ' (',
        @colName VARCHAR(50), @colType VARCHAR(50),
        @cnt INT = (SELECT COUNT(*) FROM #temp);

DECLARE cur CURSOR FOR SELECT ColName, ColType FROM #temp;
OPEN cur;
FETCH NEXT FROM cur INTO @colName, @colType;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL += @colName + CASE WHEN @cnt > 1 THEN ',' ELSE ')' END;
    SET @cnt -= 1;
    FETCH NEXT FROM cur INTO @colName, @colType;
END
CLOSE cur; DEALLOCATE cur;

-- 3. 組裝 VALUES 列
SET @strSQL += ' VALUES (';
SET @cnt = (SELECT COUNT(*) FROM #temp);

DECLARE curVal CURSOR FOR SELECT ColName, ColType FROM #temp;
OPEN curVal;
FETCH NEXT FROM curVal INTO @colName, @colType;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @val NVARCHAR(200), @sql NVARCHAR(500);
    SET @sql = N'SELECT @val = ' + QUOTENAME(@colName) + N' FROM ' + QUOTENAME(@strtablename) + N' WHERE ' + QUOTENAME(@strKeyName) + N' = @key';
    EXEC sp_executesql @sql, N'@key VARCHAR(50), @val NVARCHAR(200) OUTPUT', @key = @strKeyValue, @val = @val OUTPUT;

    IF @colType IN ('varchar','nvarchar','char','nchar','text','ntext')
        SET @strSQL += '''' + ISNULL(@val, '') + '''';
    ELSE
        SET @strSQL += ISNULL(@val, '0');

    SET @strSQL += CASE WHEN @cnt > 1 THEN ',' ELSE ')' END;
    SET @cnt -= 1;
    FETCH NEXT FROM curVal INTO @colName, @colType;
END
CLOSE curVal; DEALLOCATE curVal;

-- 4. 輸出結果
PRINT @strSQL;
DROP TABLE #temp;

輸出範例

INSERT INTO DebtApplyForm (ApplyFormId, IDNo, ApplyDate, ..., PassTime)
VALUES ('A200979469A1', 'A200979469', '20100505', ..., '')

擴充建議

• 支援複合主鍵：改用 WHERE (col1, col2) = (val1, val2) 或動態組裝 WHERE 條件。
• 支援 datetime、binary、uniqueidentifier 等型別格式化。
• 整合為預存程序 sp_GenerateInsert @TableName, @KeyName, @KeyValue 方便重用。
• 大量資料遷移建議用 BCP / SSIS / SELECT INTO，本腳本適合單筆/少量指令碼產生。