工作中遇到需要实现不同版本的数据库间,数据同步。当然了前提数据表接口相同。有了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分钟内,这个跟网络条件有一定因素。以后导数据终于不用发愁了。嘎嘎!!!!!!!!
转载于:https://blog.51cto.com/85608547/1433972