跨服务器修改表同时按照顺序关联两个没有关系的表

insert into PE_U_Recommend(ID,GuestName,GuestEmail,Mobile,Area,BuyType,GouMuDi,JutiQuwei,Yusuan,QiwanArea,Huxing,Beizhu,AdminReply) select tabletest2.GeneralID, tabletest.name ,--留言人 tabletest.email,--邮箱 tabletest.mobile,--电话 tabletest.region,--地区 tabletest.[type],--类型 tabletest.[description],--目标 个人居住 投资 tabletest.demand,--地区要求 tabletest.budget,--总价 tabletest.area,--总面积 tabletest.Household,--户型 tabletest.other,--其他要就 --ReHouse.addtime,--提问时间 tabletest.content --回答 from openrowset('SQLOLEDB' ,'222.180.65.52,2779';'sa';'pwd' ,'select table2.NOO,table1.* from (select ReHouse.id, ReHouse.name ,--留言人 ReHouse.email,--邮箱 ReHouse.mobile,--电话 ReHouse.region,--地区 ReHouse.[type],--类型 ReHouse.[description],--目标 个人居住 投资 ReHouse.demand,--地区要求 ReHouse.budget,--总价 ReHouse.area,--总面积 ReHouse.Household,--户型 ReHouse.other,--其他要就 ReHouse_Reply.content --回答 content from ReHouse inner join ReHouse_Reply on ReHouse.id=ReHouse_Reply.reId ) as table1 inner join (select ROW_NUMBER() OVER (ORDER BY SUM(ReHouse.id) asc) as ''NOO'',ReHouse.id from ReHouse inner join ReHouse_Reply on ReHouse.id=ReHouse_Reply.reId group by ReHouse.id ) as table2 on table1.id=table2.id order by table2.NOO') AS tabletest inner join (select ROW_NUMBER() OVER (ORDER BY SUM(GeneralID) asc) as 'NOO2', PE_CommonModel.GeneralID from PE_CommonModel where GeneralID>47434 and GeneralID<=47492 group by GeneralID) as tabletest2 on tabletest.NOO=tabletest2.NOO2

 

需要注意的就是 里面的排序的问题 同时常需要打开

openrowset

 

想用openrowset查询远程数据库，结果提示SQL Server 阻止了对组件 /'Ad Hoc Distributed Queries/' 的访问错误，在网上搜索了一下，找到解决方法：

启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
    使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure