跨数据库服务器查询和跨表更新.docVIP

跨数据库服务器查询和跨表更新 一.摘要 本文通过实际业务场景讲解如何实现跨数据库服务器的数据查询, 以及跨表更新的SQL语法. 二.业务场景 想实现的功能很简单, 在我的本地一个表用来保存省的信息: T_Province 在另外一台服务器上也有一个保存省的表province,其中有我本地没有的provience_name_en和provience_id信息.我希望将它们保存到我的表中. 虽然用程序很容易实现,但是我觉得如果能直接操作数据库的话将更简单,而且还能学习到跨服务器查询的知识. 三.准备工作 首先我在本地 T_Province 表中添加了 ProvinceNameEn 和 ProvinceId 两个字段.接下来就要想办法为这两个字段填充数据. 四.跨服务器查询 首先需要解决跨服务器查询的问题. 先来看我的最终实现: --创建链接服务器 exec sp_addlinkedserver @server= SQL2, @srvproduct= , @provider=SQLNCLI, @datasrc = 192.168.9.123 --登录链接服务器 exec sp_addlinkedsrvlogin @rmtsrvname = SQL2, @useself = false , @locallogin = null, @rmtuser =sa, @rmtpassword = 123456 ?上面使用sp_addlinkedserver和sp_addlinkedsrvlogin 与服务器建立了链接, 接下来就可以直接查询远程服务器上的数据了: --创建临时表 create table #t (ProvinceName nvarchar(50), ProvinceNameEn nvarchar(50), ProvinceID nvarchar(50)) INSERT INTO #t(ProvinceName, ProvinceNameEn, ProvinceID) ( SELECT localDB.ProvinceName, serverDB.province_name_en, serverDB.province_ID FROM T_Province as localDB, SQL2.bdg_web_retail.dbo.province as serverDB WHERE localDB.ProvinceName = serverDB.Province_Name ) --跨服务器查询生成的临时表结果 SELECT * FROM #t ? 通过上面的SQL语句,我将两个服务器,两个数据库的两个表做了内联查询,并且将结果保存到了本地的临时表#t中. 五.跨表更新 接下来希望将#t 中的数据更新到T_Province表中.其实跨表更新很简单, 但是一开始头脑中这个概念, 不知道set子句如何写.下面是最后的成果: --更新本地的 T_Province表数据 UPDATE T_Province SET T_Province.ProvinceNameEn = ( SELECT #t.ProvinceNameEn) , T_Province.ProvinceID = (SELECT #t.ProvinceID) FROM T_Province, #t WHERE T_Province.ProvinceName = #t.ProvinceName ? 需要注意的是我最开始使用了Declare建立表变量的形式创建了@t,但是执行update操作时提示必须声明标量变量@t, 换成了临时表#t就没有问题. 六.跨服务器查询相关知识 下面对跨服务器查询用到的知识进行讲解. 创建链接服务器 sp_addlinkedserver 创建链接服务器。链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。在使用 sp_addlinkedserver 创建链接服务器后，可对该服务器运行分布式查询。如果链接服务器定义为 SQL Server 实例，则可执行远程存储过程。 语法 sp_addlinkedserver [ @server= ] server [ , [ @srvproduct= ] product_name ] [ , [ @provider= ] provider_name ] [ , [ @datasrc= ] data_source ] [ , [ @location= ] location ] [ , [ @provstr= ] provider_string ] [ , [ @catalog= ] catalog ] 参数 [ @server = ] server 要创建的链接服务器的名称。server 的数据类型为 sy