将sqlserver表中的数据导出sql语句或生成insert into语句

--将sqlserver表中的数据导出sql语句或生成insert into语句

if Object_id('UspOutputData') is not null
    drop PROCEDURE UspOutputData;
go
CREATE PROCEDURE dbo.UspOutputData 
    @tablename sysname 
AS
BEGIN 
    declare @column varchar(1000) 
    @columndata @sql 4000) 
    @xtype tinyint 
    @name sysname 
    @objectId int 
    @objectname sysname 
    @ident int 

    set nocount on 
    set @objectId=object_id(@tablename) 

    if is null  判断对象是否存在 
    begin 
        print The object not exists' 
        return 
    end 
    @objectnamertrim(object_name(@objectId)) 

    @objectname null or charindex(@objectname,@tablename)=0 此判断不严密 
    object not in current databaseend 

    OBJECTPROPERTY(@objectId,IsTable') < > 1  判断对象是否是table 
    The object is not tableselect @ident=status&0x80 from syscolumns where id=@objectid and status0x800x80 

    null 
        SET IDENTITY_INSERT '+@TableName+ ON' 

    declare syscolumns_cursor cursor

    for select c.name,c.xtype from syscolumns c where c.id@objectid order by c.colid 

    open syscolumns_cursor 
    @column'' 
    @columndatafetch next from syscolumns_cursor into @name,0); line-height:1.5!important">@xtype 

    while @@fetch_status >-1 
    2 
        begin 
            @xtype not in(189,34,0); line-height:1.5!important">35,0); line-height:1.5!important">99,0); line-height:1.5!important">98) timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 
            begin 
                @column = + case when len(@column)then'' else ,'end + @name 
                @columndata case    @columndata)then ''' end 
                                              when 167,0); line-height:1.5!important">175) '''''''''+@name''''''''' varchar,char 
                                                        231,0); line-height:1.5!important">239) '''N''''''nvarchar,nchar 
                                                        @xtype61 +convert(char(23),121)+datetime 
                                                        58 +convert(char(16),120)+smalldatetime 
                                                        36 +convert(char(36),0); line-height:1.5!important">)+uniqueidentifier 
                                                  else @name 
                                                  end  case
            end  if 
        if
        @xtype 
    while

    close syscolumns_cursor 
    deallocate syscolumns_cursor 

    @sqlset nocount on select insert @tablename() values(as --) from @tablename 

    @sql 
    exec(@sql) 

    null 
     OFF' 
END;
GO

/*
--使用方法： 

  exec UspOutputData 你的表名 

--选择【执行模式】为“以文本显示结果”，然后将运行后的结果存成.sql，加上用SQL Server生成的数据库脚本就可以了。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!