将数据库数据导出成insert语句的存储过程

因项目需要，在网上搜索到以下的存储过程，可以将数据库中的数据导出成insert安装脚本。(原存储过程有些错误，做了点修改)

 

CREATE PROCEDURE   [dbo].[outputdata]   

  @tableName Varchar(100)--表名
AS
    DECLARE @IsIdentity INT
    DECLARE @columnName VARCHAR(100)--列名
    DECLARE @TypeName VARCHAR(100)--数据类型
    DECLARE @columns VARCHAR(8000)--
    DECLARE @columnsAndhead VARCHAR(8000)--
     
    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(50)),''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)
     
--END

评论

4 楼 風一樣的男子 2011-01-06  

工具多帮你做好了

3 楼 yuhang_java 2011-01-06  

需要这样？  难不成数据还需要移植？ 如果不移植数据库就用1楼的方法进行导出来就可以。。。  如果数据库需要移植，比如从Oracle到Mssql   那么你这个存储过程貌似还不能满足吧？

2 楼 wxlmcqueen 2011-01-05  

假设一个需求
在页面加一个event

选择到表导出数据。

如何实现？

不会查询到内存然后打印吧？

1 楼 evanzzy 2011-01-04  

导Sql语句可以用Toad，还可导成dmp、excel、txt等格式，这个功能自己写有点麻烦了