根据表名生成该表的插入语句

---恢复内容开始---

前段时间一个项目要用到数据的维护，如果数据库维护比较频繁，用传统的DTS个人认为有点麻烦，我们经常遇到不同版本表结构不同的情况，用传统的DTS做导入导出的话个人认为比较麻烦，特别是有些特殊字符不好处理，鄙人从网上找了一个不错存储过程，然后自己稍微修改了下，用起来还不错，分享下。

--根据表名生成插入语句
--2013-01-08
--exec sp_CreateInsertScript 'rooms '
create procedure sp_CreateInsertScript
(
 @tablename varchar(256)   --表名
)
as
begin
 if exists(select 0 from sysobjects where name = @tablename and xtype = 'u')
 begin
  set nocount on  --关闭影响行数的计数(避免数据量过大,减少sql执行时间)

  declare @sqlstr varchar(8000)
  declare @sqlstr1 varchar(8000)--拼接要生成的值
  declare @sqlstr2 varchar(8000)--拼接要插入的字段名
  declare @iszz varchar(1000)   --关闭自增列的的语句
  if exists(select  0 from sysobjects where objectproperty(id, 'TableHasIdentity') = 1 and upper(name) = upper(@tableName))--判断是否有自增列
  begin
   select @sqlstr='select ''set identity_insert '+@tableName+' on  insert '+@tablename+' '
   set @iszz ='  set identity_insert '+@tableName+' off  '
  end
  else begin 
   select @sqlstr='select ''insert '+@tablename+' '
   set @iszz = ''
  end

  create table #FileValue  --插入表的字段
  (
   col varchar(8000),
   colid int ,
   name varchar(250)
  )

  insert into #FileValue
   select case
   --如果是binary类型(属于二进制数据类型,从 1 到 8,000 的变值,现在系统中所以的表都没有这个类型的字段，暂时不考虑)
   --when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'''dbo.fn_ConvertVarBinary_HexStr('+a.name +')'''+' end'

   --如果是Bit类型
   when a.xtype =104 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(1),a.'+a.name +')'+' end'

   --如果是char类型
   when a.xtype =175 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace(a.'+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

   --如果是datetime类型
   when a.xtype =61 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(19),a.'+a.name +',121)'+ '+'''''''''+' end'

   --如果是decima类型
   when a.xtype =106 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),a.'+a.name +')'+' end'

   --如果是float类型
   when a.xtype =62 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(50),a.'+a.name +',2)'+' end'
   
   --如果是int类型
   when a.xtype =56 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(20),a.'+a.name +')'+' end'

   --如果是money类型
   when a.xtype =60 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(50),a.'+a.name +')'+' end'

   --如果是nchar类型
   when a.xtype =239 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace(a.'+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

   --如果是numeric类型
   when a.xtype =108 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),a.'+a.name +')'+' end'

   --如果是nvarchar类型
   when a.xtype =231 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace(a.'+a.name+','''''''','''''''''''')' + '+'''''''''+' end'

   --如果是real类型
   when a.xtype =59 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(50),a.'+a.name +',2)'+' end'

   --如果是smalldatetime类型
   when a.xtype =58 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(50),a.'+a.name +',121)'+ '+'''''''''+' end'

   --如果是smallint类型
   when a.xtype =52 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(20),a.'+a.name +')'+' end'

   --如果是smallmoney类型
   when a.xtype =122 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(50),a.'+a.name +')'+' end'

   --如果是bigint类型
   when a.xtype =127 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(20),a.'+a.name +')'+' end'

   --如果是tinyint类型
   when a.xtype =48 then 'case when a.'+a.name+' is null then ''NULL'' else '+'convert(varchar(20),a.'+a.name +')'+' end'

   --如果是varbinary类型(这里面调了另一个函数处理它，转换成字符)
   when a.xtype =165 then 'case when a.'+a.name+' is null then ''NULL'' else '+'dbo.fn_varbinaryTostr(a.'+a.name +')'+' end'

   --如果是varchar类型
   when a.xtype =167 then 'case when a.'+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace(a.'+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
   
   --如果是image类型(一直没有解决办法,期待高人,目前的解决办法是想转成图片到本地，需要的时候转成码更新对应的字段)
   --when a.xtype =34 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varbinary(8000),'+a.name +')'+' end'    

   ---如果是text类型
   when a.xtype =35 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace(convert(varchar(8000),a.'+a.name +'),'''''''','''''''''''')' + '+'''''''''+' end'
   else '''NULL'''
   end as col,a.colid,a.name
   from syscolumns a where a.id = object_id(@tablename) 
   and a.xtype <>189 and a.xtype <>34 and a.xtype <>173 and  a.xtype <>36

   select @sqlstr1=''
   select @sqlstr2='('
   select @sqlstr1='values (''+'
   select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from  #FileValue order by colid
   --print @sqlstr1
   set @sqlstr2 = left(@sqlstr2,len(@sqlstr2)-1)+') '
   set @sqlstr1 = left(@sqlstr1,len(@sqlstr1)-3)+ ')   '+@iszz+' '' from ' +@tablename+' a'+char(13)
   declare @QueryCondition varchar(8000) --链表字符串或者查询条件字符串
   set @QueryCondition = dbo.fn_CreateQueryCondition(@tablename)
   exec( @sqlstr+@sqlstr2+@sqlstr1+@QueryCondition)
   --select * from #FileValue
   drop table #FileValue
   set nocount off  --打开计数
 end
end
go

这里面用到了几个函数：

--根据表名返回条件语句(示例而已，返回什么样的条件根据自身的需要来写)
--吴俊,2013-02-05
alter function dbo.fn_CreateQueryCondition
(
    @tableName    varchar(250)
)
returns varchar(8000)
as
begin
    declare @sqlwhere varchar(8000)
    set @sqlwhere = ''
    --获取连锁店类型
    declare @StoParameter_Name  varchar(10) 
    set @StoParameter_Name = ''
    select @StoParameter_Name = StoParameter_Value from StoParameter where StoParameter_Name = '连锁店类型'    
    --是否启用代销管理
    declare @isOpenDx varchar(10) 
    select @isOpenDx = isnull(StoParameter_Value,'否') from StoParameter where StoParameter_Name = '启用代销管理'
    if exists(select 0 from sysobjects where name = @tableName and xtype = 'u' )
    begin
        if(@tableName = 'StoBillDetail')
        begin
            if (@isOpenDx = '否' or exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 0))
            begin
                set @sqlwhere = 'inner join StoBill b on b.StoBill_ID = a.StoBillDetail_BillID and b.StoBill_IsAccount <> 1 and (b.StoBill_BillType > 0 and b.StoBill_BillType not in(10,11) )
                                inner join StoBillsType c on b.StoBill_TypeID = c.StoBillsType_ID and c.StoBillsType_CanChangeStoreNum = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (c.StoBillsType_Name <> ''连锁采购进货'' and c.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoSupplier d on b.StoBill_VendorID = d.StoSupplier_ID 
                                and d.StoSupplier_IsDelete = 0 and ('''+@isOpenDx+''' = ''否'' or d.StoSupplier_Type = 0) '
            end
            if (@isOpenDx = '是' and exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 1))
            begin
                set @sqlwhere = 'inner join StoBillsType b on b.StoBillsType_ID = a.StoBillDetail_BillTypeID and b.StoBillsType_InSupplierPay = 1
                                and ('''+@StoParameter_Name+ '''<> ''分店'' or (b.StoBillsType_Name <> ''连锁采购进货'' and b.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoRecipeBindWithSupplier c on a.StoBillDetail_WriteStoreRecipeID = c.StoRecipeBindWithSupplier_RecipeID 
                                inner join StoSupplier d on c.StoRecipeBindWithSupplier_SupplierID = d.StoSupplier_ID 
                                and d.StoSupplier_IsDelete = 0 and d.StoSupplier_Type = 1
                                inner join StoBill e on e.StoBill_ID = a.StoBillDetail_BillID and (e.StoBill_BillType in(1,13,14,15) or (e.StoBill_BillType > 0 and  '''+@StoParameter_Name+ ''' = ''总店'' and e.StoBill_Description like ''误差处理%''))
                                where a.StoBillDetail_IsAccount = 0 '
            end
        end
        else if(@tableName = 'StoBill')
        begin
            if (@isOpenDx = '否' or exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 0))
            begin
                --若未启用代销管理，所有供应商以购销方式处理；否则，只有购销类供应商从库存单据中获取
                set @sqlwhere = 'inner join StoBillsType b on a.StoBill_TypeID = b.StoBillsType_ID and b.StoBillsType_CanChangeStoreNum = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (b.StoBillsType_Name <> ''连锁采购进货'' and b.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoSupplier c on a.StoBill_VendorID = c.StoSupplier_ID 
                                and c.StoSupplier_IsDelete = 0 and ('''+@isOpenDx+''' = ''否'' or c.StoSupplier_Type = 0)
                                where a.StoBill_IsAccount <> 1 and (a.StoBill_BillType > 0 and a.StoBill_BillType not in(10,11)) '
            end
            if (@isOpenDx = '是' and exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 1))
            begin
                set @sqlwhere =    'inner join StoBillDetail b on a.StoBill_ID = b.StoBillDetail_BillID and b.StoBillDetail_IsAccount = 0
                                inner join StoBillsType c on c.StoBillsType_ID = b.StoBillDetail_BillTypeID and c.StoBillsType_InSupplierPay = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (c.StoBillsType_Name <> ''连锁采购进货'' and c.StoBillsType_Name <> ''连锁采购退货''))
                                inner join StoRecipeBindWithSupplier d on b.StoBillDetail_WriteStoreRecipeID = d.StoRecipeBindWithSupplier_RecipeID 
                                inner join StoSupplier e on d.StoRecipeBindWithSupplier_SupplierID = e.StoSupplier_ID 
                                and e.StoSupplier_IsDelete = 0 and e.StoSupplier_Type = 1
                                where a.StoBill_BillType in(1,13,14,15) or (a.StoBill_BillType > 0 and '''+@StoParameter_Name+''' = ''总店'' and a.StoBill_Description like ''误差处理%'')'
            end
        end
        --处理Old表
        else if(@tableName = 'StoBillDetailOld')
        begin
            if (@isOpenDx = '否' or exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 0))
            begin
                set @sqlwhere = 'inner join StoBillOld b on b.StoBill_ID = a.StoBillDetail_BillID and b.StoBill_IsAccount <> 1 and (b.StoBill_BillType > 0 and b.StoBill_BillType not in(10,11) )
                                inner join StoBillsType c on b.StoBill_TypeID = c.StoBillsType_ID and c.StoBillsType_CanChangeStoreNum = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (c.StoBillsType_Name <> ''连锁采购进货'' and c.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoSupplier d on b.StoBill_VendorID = d.StoSupplier_ID 
                                and d.StoSupplier_IsDelete = 0 and ('''+@isOpenDx+''' = ''否'' or d.StoSupplier_Type = 0) '
            end
            if (@isOpenDx = '是' and exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 1))
            begin
                set @sqlwhere = 'inner join StoBillsType b on b.StoBillsType_ID = a.StoBillDetail_BillTypeID and b.StoBillsType_InSupplierPay = 1
                                and ('''+@StoParameter_Name+ '''<> ''分店'' or (b.StoBillsType_Name <> ''连锁采购进货'' and b.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoRecipeBindWithSupplier c on a.StoBillDetail_WriteStoreRecipeID = c.StoRecipeBindWithSupplier_RecipeID 
                                inner join StoSupplier d on c.StoRecipeBindWithSupplier_SupplierID = d.StoSupplier_ID 
                                and d.StoSupplier_IsDelete = 0 and d.StoSupplier_Type = 1
                                inner join StoBillOld e on e.StoBill_ID = a.StoBillDetail_BillID and (e.StoBill_BillType in(1,13,14,15) or (e.StoBill_BillType > 0 and '''+@StoParameter_Name+ ''' = ''总店'' and e.StoBill_Description like ''误差处理%''))
                                where a.StoBillDetail_IsAccount = 0 '
            end
        end
        else if(@tableName = 'StoBillOld')
        begin
            if (@isOpenDx = '否' or exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 0))
            begin
                --若未启用代销管理，所有供应商以购销方式处理；否则，只有购销类供应商从库存单据中获取
                set @sqlwhere = 'inner join StoBillsType b on a.StoBill_TypeID = b.StoBillsType_ID and b.StoBillsType_CanChangeStoreNum = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (b.StoBillsType_Name <> ''连锁采购进货'' and b.StoBillsType_Name <>''连锁采购退货''))
                                inner join StoSupplier c on a.StoBill_VendorID = c.StoSupplier_ID 
                                and c.StoSupplier_IsDelete = 0 and ('''+@isOpenDx+''' = ''否'' or c.StoSupplier_Type = 0)
                                where a.StoBill_IsAccount <> 1 and (a.StoBill_BillType > 0 and a.StoBill_BillType not in(10,11)) '
            end
            if (@isOpenDx = '是' and exists(select top 1 StoSupplier_ID from StoSupplier where StoSupplier_Type = 1))
            begin
                set @sqlwhere =    'inner join StoBillDetailOld b on a.StoBill_ID = b.StoBillDetail_BillID and b.StoBillDetail_IsAccount = 0
                                inner join StoBillsType c on c.StoBillsType_ID = b.StoBillDetail_BillTypeID and c.StoBillsType_InSupplierPay = 1
                                and ('''+@StoParameter_Name+''' <> ''分店'' or (c.StoBillsType_Name <> ''连锁采购进货'' and c.StoBillsType_Name <> ''连锁采购退货''))
                                inner join StoRecipeBindWithSupplier d on b.StoBillDetail_WriteStoreRecipeID = d.StoRecipeBindWithSupplier_RecipeID 
                                inner join StoSupplier e on d.StoRecipeBindWithSupplier_SupplierID = e.StoSupplier_ID 
                                and e.StoSupplier_IsDelete = 0 and e.StoSupplier_Type = 1
                                where a.StoBill_BillType in(1,13,14,15) or (a.StoBill_BillType > 0 and '''+@StoParameter_Name+''' = ''总店'' and a.StoBill_Description like ''误差处理%'')'
            end
        end

        else if(@tableName = 'StoReceiveAndPayBill')
        begin
            set @sqlwhere = ' where StoReceiveAndPayBill_BillState = 13 and StoReceiveAndPayBill_IsDraft = 0 '
        end
        else if(@tableName = 'StoReceivePay')
        begin
            set @sqlwhere = ' where StoReceivePay_IsDraft = 0'
        end
        --存酒系统未支取、部分支取的单据及其明细
        else if (@tableName = 'DepositBill')
        begin
            set @sqlwhere =
            ' 
                where  DepositBill_BillType = 0 and DepositBill_ID in 
                (
                    select distinct DepositDetail_DepositID from DepositDetail
                    where DepositDetail_BillType = 0 and DepositDetail_State = 0
                ) 
            '
        end
        else if (@tableName = 'DepositDetail')
        begin
            set @sqlwhere = 'where DepositDetail_BillType = 0 and DepositDetail_State = 0    '
        end
        else if(@tableName = 'systemsettinginfo')
        begin
            set @sqlwhere = ' where Settinginfo_Name <> '' version '' '
        end
    end
    return @sqlwhere
end
go

--将varbinary转换成varchar
--吴俊,2013-01-07
Create function dbo.fn_varbinaryTostr
(
    @bin varbinary(8000)
)
returns varchar(8000)
as
begin
    declare @re varchar(8000),@i int
    select @re='',@i=datalength(@bin)
    while @i>0
        select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
                +substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
                +@re
            ,@i=@i-1
    return('0x'+@re)
end
go

---恢复内容结束---