SQL Server資料庫跨資料庫伺服器查詢和跨表更新的相關知識是本文我們主要要介紹的內容，接下來我們就通過一個例項來介紹這一過程。例項是這樣的：想實現的功能很簡單, 在我的本地一個表用來儲存省的資訊: 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)
ROM 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 的資料型別為 sysname，沒有預設值。
[ @srvproduct = ] 'product_name'
要新增為連結伺服器的OLE DB 資料來源的產品名稱。product_name 的資料型別為 nvarchar(128)，預設值為 NULL。如果為 SQL Server，則不必指定 provider_name、data_source、location、provider_string 和 catalog。
[ @provider = ] 'provider_name'
與此資料來源對應的 OLE DB 訪問介面的唯一程式設計識別符號 (PROGID)。對於當前計算機中安裝的指定 OLE DB 訪問介面，provider_name 必須唯一。provider_name 的資料型別為 nvarchar(128)，預設值為 NULL；但如果忽略 provider_name，則使用 SQLNCLI。SQLNCLI 是 SQL 本機 OLE DB 訪問介面。OLE DB 訪問介面應以指定的 PROGID 在登錄檔中註冊。
[ @datasrc = ] 'data_source'
由OLE DB 訪問介面解釋的資料來源的名稱。data_source 的資料型別為 nvarchar(4000)。data_source 作為 DBPROP_INIT_DATASOURCE 屬性傳遞以初始化 OLE DB 訪問介面。
[ @location = ] 'location'
由 OLE DB 訪問介面解釋的資料庫的位置。location 的資料型別為 nvarchar(4000)，預設值為 NULL。location 作為 DBPROP_INIT_LOCATION 屬性傳遞以初始化 OLE DB 訪問介面。
[ @provstr = ] 'provider_string'
OLE DB 訪問介面特定的連線字串，它可標識唯一的資料來源。provider_string 的資料型別為 nvarchar(4000)，預設值為 NULL。provstr 或傳遞給 IDataInitialize 或設定為 DBPROP_INIT_PROVIDERSTRING 屬性以初始化 OLE DB 訪問介面。
在針對 SQL 本機客戶端 OLE DB 訪問介面建立連結伺服器後，可將 SERVER 關鍵字用作 SERVER=servername\instancename 來指定例項，以指定特定的 SQL Server 例項。servername 是執行 SQL Server 的計算機名稱，instancename 是使用者將連線到的特定 SQL Server 例項的名稱。
[ @catalog = ] 'catalog'
與 OLE DB 訪問介面建立連線時所使用的目錄。catalog 的資料型別為 sysname，預設值為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以初始化 OLE DB 訪問介面。在針對 SQL Server 例項定義連結伺服器時，目錄指向連結伺服器對映到的預設資料庫。
登入連結伺服器 sp_addlinkedsrvlogin
語法
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'useself' ] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ]
引數
[ @rmtsrvname = ] 'rmtsrvname'
應用登入對映的連結伺服器的名稱。rmtsrvname 的資料型別為 sysname，沒有預設值。
[ @useself = ] 'useself'
確定用於連線遠端伺服器的登入名。useself 的資料型別為 varchar(8)，預設值為 TRUE。
值為true 時指定登入使用自己的憑據連線 rmtsrvname，忽略 rmtuser 和 rmtpassword 引數。false 指定使用 rmtuser 和 rmtpassword 引數連線指定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也設定為 NULL，則不使用登入名或密碼來連線連結伺服器。
[ @locallogin = ] 'locallogin'
本地伺服器上的登入。locallogin 的資料型別為 sysname，預設值為 NULL。NULL 指定此項應用於連線到 rmtsrvname 的所有本地登入。如果不為 NULL，則 locallogin 可以是 SQL Server 登入或Windows 登入。對於 Windows 登入來說，必須以直接的方式或通過已被授權訪問的 Windows 組成員身份授予其訪問 SQL Server 的許可權。
[ @rmtuser = ] 'rmtuser'
當 useself 為 false 時，表示用於連線 rmtsrvname 的使用者名稱。rmtuser 的資料型別為 sysname，預設值為 NULL。
[ @rmtpassword = ] 'rmtpassword'
與 rmtuser 關聯的密碼。rmtpassword 的資料型別為 sysname，預設值為 NULL。
使用連結伺服器
伺服器名.資料庫名.dbo.表名
刪除連結伺服器 sp_dropserver
語法
sp_dropserver [ @server = ] 'server' [ , [ @droplogins = ] { 'droplogins' NULL} ]
引數
[ @server = ] 'server'
要刪除的伺服器。server 的資料型別為 sysname，無預設值。server 必須存在。
[ @droplogins = ] 'droplogins' NULL
指示如果指定了 droplogins，那麼對於server，還必須刪除相關的遠端伺服器和連結伺服器登入名。@droplogins 的資料型別為 char(10)，預設值為 NULL。
關於SQL Server跨資料庫伺服器查詢和跨表更新的相關知識就介紹到這裡了，希望本次的介紹能夠對您有所收穫！