• 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 '表名'

  • 相关阅读:
    2016"百度之星"
    ZOJ 3703 Happy Programming Contest(01背包的灵活运用)
    LA 3942 Remember the Word (Trie树)
    ZOJ 3700 Ever Dream(Vector)
    Hdoj 1686 Oulipo
    2017总结,2018计划
    Ubuntu16.04 + caffe-ssd + [CPU_ONLY] + KITTI 训练总结
    【转载】The Elements of Programming Style之代码风格金科玉律
    qt中setStyleSheet导致的内存泄漏
    【转】用枚举定义有意义的数组下标
  • 原文地址:https://www.cnblogs.com/djcsch2001/p/2796441.html
Copyright © 2020-2023  润新知