• SQL Server 2005导出表中数据的SQL脚本形式(即INSERT语句)


     1 CREATE   proc spGenInsertSQL (@tablename varchar(256))
     2 as
     3 begin
     4 declare @sql varchar(8000)
     5 declare @sqlValues varchar(8000)
     6 set @sql =' ('
     7 set @sqlValues = 'values (''+'
     8 select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
     9 from
    10       (select case
    11                 when xtype in (48,52,56,59,60,62,104,106,108,122,127)       
    12 
    13                      then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
    14 
    15                 when xtype in (58,61)
    16 
    17                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
    18 
    19                when xtype in (167)
    20 
    21                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
    22 
    23                 when xtype in (231)
    24 
    25                      then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
    26 
    27                 when xtype in (175)
    28 
    29                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
    30 
    31                 when xtype in (239)
    32 
    33                      then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
    34 
    35                 else '''NULL'''
    36 
    37               end as Cols,name
    38 
    39          from syscolumns 
    40 
    41         where id = object_id(@tablename)
    42 
    43       ) T
    44 set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
    45 print @sql
    46 exec (@sql)
    47 end
    48 GO

    使用方法: exec spGenInsertSQL '表名'

  • 相关阅读:
    Hadoop学习笔记
    Hadoop学习笔记 -伪分布式
    SSH 连接报错总结
    Hadoop学习笔记
    Trie 前缀树/字典树
    解数独(Leetcode-37 / HDU-1426)/回溯/状态压缩
    MyBatis 多表关联查询
    python_37期自动化【lemon】
    api课堂笔记_day14
    api课堂笔记_day12&day13
  • 原文地址:https://www.cnblogs.com/djcsch2001/p/2796441.html
Copyright © 2020-2023  润新知