執(zhí)行生成INSERT的存儲過程（每行數(shù)據(jù)是一條Insert語句）

USE [MyDB]
GO
DECLARE        @return_value int
EXEC        @return_value = [dbo].[DBGenData]
                @tableName = N'tb_web_sysrolelist_info'
SELECT        'Return Value' = @return_value
GO

創(chuàng)建生成INSERT的存儲過程

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[DBGenData]
    @tableName Varchar(100)--表名
AS
    DECLARE @IsIdentity INT
    DECLARE @columnName VARCHAR(100)--列名
    DECLARE @TypeName VARCHAR(100)--數(shù)據(jù)類型
    DECLARE @columns VARCHAR(3000)--
    DECLARE @columnsAndhead VARCHAR(3000)--

    SET @columnsAndhead = 'INSERT INTO '+@tableName+'(['
    SET @columns = ''

    --獲取表的所有字段名稱
    DECLARE a CURSOR FOR
        SELECT COLUMNPROPERTY( a.id,a.name,'IsIdentity') AS IsIdentity, a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
        FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype AND b.xtype=b.xusertype
        WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)

    OPEN a

    FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @IsIdentity =0
                    BEGIN
                        IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
                            BEGIN
                                SET @columns = @columns + 'ISNULL(CAST(['+@columnName +'] AS VARCHAR),''NULL'')+'',''+'
                            END
                        ELSE
                            BEGIN
                                SET @columns = @columns+ '''''''''+ ISNULL(CAST(['+@columnName +'] AS VARCHAR),''NULL'')+ ''''''''+'',''+'
                            END
                        SET @columnsAndhead = @columnsAndhead + ''+ @columnName +'],['
                    END
                FETCH NEXT FROM a INTO @IsIdentity, @columnName ,@TypeName
             END

    SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-2) +') VALUES('''
    SELECT @columns = left(@columns,len(@columns)-5)

    CLOSE a
    DEALLOCATE a
    exec('select '''+@columnsAndhead+'+'+@columns +'+'')'' as InsertSQL from '+@tableName)