SQL Server 2005 以預存程序匯出資料為 INSERT 腳本

使用方式

EXEC KeleyiOutputSqlData 'TargetTableName';

預存程式碼

SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; GO

CREATE PROCEDURE [dbo].[KeleyiOutputSqlData]
    @tablename sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @objectId INT = OBJECT_ID(@tablename);
    IF @objectId IS NULL BEGIN PRINT 'The object not exists'; RETURN; END
    IF OBJECTPROPERTY(@objectId, 'IsTable') <> 1 BEGIN PRINT 'The object is not table'; RETURN; END

    DECLARE @ident INT;
    SELECT @ident = status & 0x80 FROM syscolumns WHERE id = @objectId AND status & 0x80 = 0x80;
    IF @ident IS NOT NULL PRINT 'SET IDENTITY_INSERT ' + @tablename + ' ON';

    DECLARE @column NVARCHAR(MAX) = '', @columndata NVARCHAR(MAX) = '';
    DECLARE @name SYSNAME, @xtype TINYINT;

    DECLARE cur CURSOR FOR
        SELECT c.name, c.xtype FROM syscolumns c WHERE c.id = @objectId ORDER BY c.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 '' ELSE ',' END + @name;
            SET @columndata += CASE WHEN LEN(@columndata)=0 THEN '' ELSE ','',''' END +
                CASE @xtype
                    WHEN 167 THEN ''''''''+@name+'''''''' -- varchar
                    WHEN 175 THEN ''''''''+@name+'''''''' -- char
                    WHEN 231 THEN '''N''''''+'+@name+'+''''''''' -- nvarchar
                    WHEN 239 THEN '''N''''''+'+@name+'+''''''''' -- nchar
                    WHEN 61  THEN ''''''''+CONVERT(CHAR(23),'+@name+',121)+''''''''' -- datetime
                    WHEN 58  THEN ''''''''+CONVERT(CHAR(16),'+@name+',120)+''''''''' -- smalldatetime
                    WHEN 36  THEN ''''''''+CONVERT(CHAR(36),'+@name+')+''''''''' -- uniqueidentifier
                    ELSE @name
                END;
        END
        FETCH NEXT FROM cur INTO @name, @xtype;
    END
    CLOSE cur; DEALLOCATE cur;

    DECLARE @sql NVARCHAR(MAX) = 
        'SET NOCOUNT ON SELECT ''INSERT ' + @tablename + '(' + @column + ') VALUES('' AS ''--'',' + @columndata + ','')'' FROM ' + @tablename;
    PRINT '--' + @sql;
    EXEC(@sql);

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

特點

• 自動處理身分識別欄位（SET IDENTITY_INSERT ON/OFF）。
• 支援常見資料型別轉字串（datetime 用 ISO 8601 格式 121）。
• 略過 timestamp、LOB 型別，避免腳本過大。

適用場景

小型資料表遷移、測試資料版控、跨版本匯入（如 2005 → 2019）。