---恢复内容开始---
前段时间一个项目要用到数据的维护，如果数据库维护比较频繁，用传统的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
---恢复内容结束---