数据库端数据转移

    工作中遇到需要实现不同版本的数据库间，数据同步。当然了前提数据表接口相同。有了2个多小时时间写了一个支持批量多张数据表进行有条件的数据转移.不受标识列的限制。如有不周之处还请大家积极批评指正。

----------***************************************************************************************

if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].proc_TransferDateFormSourceToGoal‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
-- 删除存储过程
drop procedure [dbo].proc_TransferDateFormSourceToGoal
GO
-- 支持批量多张数据表进行有条件的数据转移.不受标识列的限制
create proc proc_TransferDateFormSourceToGoal
(
	@sourceTableNames nvarchar(2000),  --数据源数据表,多表以逗号隔开,最后以逗号结尾 如 ‘T_meterInformation,T_basicInfo,‘
	@sourceParameters nvarchar(2000), --数据源数据表对应的条件,表以逗号隔开,最后以逗号结尾.无条件为空逗号如‘1=1,,‘
	@sourceDataBaseName	 nvarchar(2000),--数据源数据库名称
	@goalTableNames nvarchar(2000),--目标数据源数据表,多表以逗号隔开,最后以逗号结尾 如 ‘T_meterInformation,T_basicInfo,‘
	@sourceIPAndProt	nvarchar(100),--数据源ip地址和端口号，默认1433可以不用拼写端口号。如‘192.168.12.60:1433‘(‘192.168.12.60‘)
	@sourceUserName nvarchar(200),--数据源登陆账户名
	@sourcePassWord nvarchar(200)--数据源登陆密码
)
as
	set nocount on
	
	declare @ErrorCount int
	set @ErrorCount=0
	begin --main proc
		declare @loginName nvarchar(20)
		set @loginName=‘srv_lnk‘
		exec   sp_addlinkedserver     @loginName, ‘ ‘, ‘SQLOLEDB ‘, @sourceIPAndProt
		exec   sp_addlinkedsrvlogin   @loginName, ‘false ‘,null,@sourceUserName, @sourcePassWord
		
		declare @OperatedTableList	 table
		(
			tableName nvarchar(200),
			parameterStr nvarchar(200),
			goalTableName nvarchar(200)
		)
		
	
		declare @tableNameTemp nvarchar(200),@parameterStr nvarchar(200),@goalTableNameTemp nvarchar(200)
		set	 @tableNameTemp=‘‘
		set	@parameterStr=‘‘
		set	@goalTableNameTemp=‘‘
		while LEN(@sourceTableNames)>0
		begin
			--处理数据表列表
			set @tableNameTemp=LEFT(@sourceTableNames,charindex(‘,‘,@sourceTableNames)-1)			
			set @sourceTableNames=SUBSTRING(@sourceTableNames,len(@tableNameTemp)+2,LEN(@sourceTableNames))
			--处理数据表列表
			set @parameterStr=LEFT(@sourceParameters,charindex(‘,‘,@sourceParameters)-1)			
			set @sourceParameters=SUBSTRING(@sourceParameters,len(@parameterStr)+2,LEN(@sourceParameters))
			--Test
			--select @tableNameTemp,@tableNames,@parameterStr,@parameters
			
			set @goalTableNameTemp=LEFT(@goalTableNames,charindex(‘,‘,@goalTableNames)-1)			
			set @sourceParameters=SUBSTRING(@goalTableNames,len(@goalTableNameTemp)+2,LEN(@goalTableNames))
			
			insert into @OperatedTableList
			select @tableNameTemp,@parameterStr,@goalTableNameTemp
			
			set	 @tableNameTemp=‘‘
			set	@parameterStr=‘‘
			set	@goalTableNameTemp=‘‘
		end
		
		begin --循环遍历 操作数据 
			declare	 Temp_Cursor cursor for
			select tableName,parameterStr,goalTableName from @OperatedTableList
			
			declare @ParameterStrTemp nvarchar(200)
			
			Set	 @ParameterStrTemp=‘‘
			set	@tableNameTemp=‘‘
			set	@goalTableNameTemp=‘‘
			
			
			open Temp_Cursor
			fetch next from Temp_Cursor into @tableNameTemp,@ParameterStrTemp,@goalTableNameTemp
			while @@FETCH_STATUS=0
			begin
				if	 LEN(@ParameterStrTemp)<>0
				begin
					set @ParameterStrTemp=‘where ‘+@ParameterStrTemp
				end
				
				declare @columns nvarchar(500)
				set @columns=‘‘

				SELECT   @columns=isnull(@columns+‘,‘,‘‘) +a.name FROM    syscolumns a  inner join    sysobjects d
				on
					a.id=d.id  and d.xtype=‘U‘ and  d.name<>‘dtproperties‘
				where
					d.name=@goalTableNameTemp
				   
				 set @columns=RIGHT(@columns,LEN(@columns)-1) 
				
				exec 	(
				‘
				
				 begin tran
				if exists (select * from ‘+@loginName+‘.‘+@sourceDataBaseName+‘.dbo.sysobjects where name=‘‘‘+@tableNameTemp+‘‘‘ ) and 		exists (select * from dbo.sysobjects where id = object_id(N‘‘dbo.‘+@tableNameTemp+‘‘‘) and OBJECTPROPERTY(id, N‘‘IsUserTable‘‘) = 1) and exists(select   *   from   syscolumns   where   id=object_id(N‘‘‘+@goalTableNameTemp+‘‘‘)    and   COLUMNPROPERTY(id,name,‘‘IsIdentity‘‘)=1)
				begin
						
						SET IDENTITY_INSERT ‘+@goalTableNameTemp+‘ ON
						insert into ‘+@goalTableNameTemp+‘(‘+@columns+‘)
						select * from ‘+@loginName+‘.‘+@sourceDataBaseName+‘.dbo.‘+@tableNameTemp+‘ ‘+@ParameterStrTemp+‘
						SET IDENTITY_INSERT ‘+@goalTableNameTemp+‘ OFF
						
				end	
				else if 
					 exists (select * from ‘+@loginName+‘.‘+@sourceDataBaseName+‘.dbo.sysobjects where name = ‘‘‘+@tableNameTemp+‘‘‘ ) and 		exists (select * from dbo.sysobjects where id = object_id(N‘‘dbo.‘+@tableNameTemp+‘‘‘) and OBJECTPROPERTY(id, N‘‘IsUserTable‘‘) = 1) 
				begin
					insert into ‘+@goalTableNameTemp+‘(‘+@columns+‘)
					select *  from ‘+@loginName+‘.‘+@sourceDataBaseName+‘.dbo.‘+@tableNameTemp+‘ ‘+@ParameterStrTemp+‘
				end		
				if @@error>0
				rollback tran
				else
				commit tran
				‘)
				set @ErrorCount=@ErrorCount+@@ERROR
				fetch next from Temp_Cursor into @tableNameTemp,@ParameterStrTemp,@goalTableNameTemp
			end
			close Temp_Cursor
			deallocate Temp_Cursor
		end
		
		
	end
	   exec sp_dropserver   @loginName, ‘droplogins ‘  
	set nocount off
go

             



--测试语句
--exec proc_TransferDateFormSourceToGoal  ‘T_HouseDetailD,‘,‘,‘,‘db_meterReadingData‘,‘T_HouseDetailD,‘,‘221.204.238.48‘,‘sa‘,‘huizhong‘

 --初步测试结果：单表无条件插入百万级数据实际耗时在30分钟内，这个跟网络条件有一定因素。以后导数据终于不用发愁了。嘎嘎！！！！！！！！

本文出自 “洛山红茶的成长” 博客，请务必保留此出处http://85608547.blog.51cto.com/2093443/1433972

数据库端数据转移,古老的榕树,5-wow.com