工作中遇到需要实现不同版本的数据库间，数据同步。当然了前提数据表接口相同。有了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_TransferDateFormSourceToGoalGO-- 支持批量多张数据表进行有条件的数据转移.不受标识列的限制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 offgo --测试语句--exec proc_TransferDateFormSourceToGoal 'T_HouseDetailD,',',','db_meterReadingData','T_HouseDetailD,','221.204.238.48','sa','huizhong' --初步测试结果：单表无条件插入百万级数据实际耗时在30分钟内，这个跟网络条件有一定因素。以后导数据终于不用发愁了。嘎嘎！！！！！！！！