SQL Server跨庫訪問

標籤：blog   使用   資料   div   log   ad   sp   on   

MSSQLServer不同伺服器資料庫之間的資料操作

 

方法1:

--建立連結的伺服器

exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘遠程伺服器名或ip地址‘

exec sp_addlinkedsrvlogin  ‘ITSV ‘, ‘false ‘,null, ‘使用者名稱‘, ‘密碼‘

 

--查詢樣本

select * from ITSV.資料庫名.dbo.表名

 

--匯入樣本

select * into 表 from ITSV.資料庫名.dbo.表名

 

--以後不再使用時刪除連結的伺服器

exec sp_dropserver  ‘ITSV ‘, ‘droplogins ‘

 

方法2:

--串連遠程/區域網路資料(openrowset/openquery/opendatasource)

--1、openrowset

 

--查詢樣本

select * from openrowset( ‘SQLOLEDB ‘, ‘sql伺服器名 ‘; ‘使用者名稱 ‘; ‘密碼 ‘,資料庫名.dbo.表名)

 

--產生本地表

select * into 表 from openrowset( ‘SQLOLEDB ‘, ‘sql伺服器名 ‘; ‘使用者名稱 ‘; ‘密碼 ‘,資料庫名.dbo.表名)

 

--把本地表匯入遠端資料表

insert openrowset( ‘SQLOLEDB ‘, ‘sql伺服器名 ‘; ‘使用者名稱 ‘; ‘密碼 ‘,資料庫名.dbo.表名)

select *from 本地表

 

--更新本地表

update b

set b.列A=a.列A

 from openrowset( ‘SQLOLEDB ‘, ‘sql伺服器名 ‘; ‘使用者名稱 ‘; ‘密碼 ‘,資料庫名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

 

方法3:

--openquery用法需要建立一個串連

--首先建立一個串連建立連結的伺服器

exec sp_addlinkedserver   ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘遠程伺服器名或ip地址 ‘

--查詢

select *

FROM openquery(ITSV,  ‘SELECT *  FROM 資料庫.dbo.表名 ‘)

--把本地表匯入遠端資料表

insert openquery(ITSV,  ‘SELECT *  FROM 資料庫.dbo.表名 ‘)

select * from 本地表

--更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV,  ‘SELECT * FROM 資料庫.dbo.表名 ‘) as a 

inner join 本地表 b on a.列A=b.列A

 

方法4:

--3、opendatasource/openrowset

SELECT   *

FROM   opendatasource( ‘SQLOLEDB ‘,  ‘Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ‘ ).test.dbo.roy_ta

--把本地表匯入遠端資料表

insert opendatasource( ‘SQLOLEDB ‘,  ‘Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ‘).資料庫.dbo.表名

select * from 本地表

/************** 自我整理 ****************/
/* 建立遠端資料庫串連*/EXEC sp_addlinkedserver ‘ITSV‘,//遠端連線名 ‘ ‘, ‘SQLOLEDB‘, ‘192.168.200.3\SQLEXPRESS‘;// 要訪問的伺服器/* 登入資料庫伺服器*/EXEC sp_addlinkedsrvlogin ‘ITSV‘,//遠端連線名 ‘false‘, NULL, ‘sa‘,//遠端資料庫登入名稱 ‘sa123‘;//遠端資料庫登入密碼/* 測試跨庫查詢是否執行*/SELECT *FROM
ITSV.DBVideo.dbo.TD_Camera_LIST;[遠端連線名].[ 資料庫名].dbo.[該資料庫下的目標表名]/* 刪除遠端資料庫串連*/EXEC sp_droplinkedsrvlogin ‘ITSV‘, NULL;EXEC sp_dropserver ‘ITSV‘;EXEC sp_dropserver ‘ITSV‘, ‘droplogins‘;/* 查看已存在的串連實體類*/sp_helpserver SELECT * FROM sys.sysservers

 附:

預存程序名/視圖名 作用 舉例

sp_addlinkedserver 註冊遠端資料庫執行個體 exec sp_addlinkedserver ‘InstanceName’

sp_dropserver 刪除遠端資料庫執行個體 exec sp_dropserver ‘InstanceName’

sp_addlinkedsrvlogin 註冊遠程執行個體登陸訪問帳戶 exec sp_addlinkedsrvlogin ‘InstanceName’, null

sp_droplinkedsrvlogin 刪除遠程執行個體登陸訪問帳戶 EXEC sp_droplinkedsrvlogin ‘InstanceName‘,‘UserName‘

sp_helpserver 當前執行個體登入的可訪問的執行個體(即查看使用sp_addlinkedserver登入過的執行個體) sp_helpserver

sys.sysservers 功能同sp_helpserver select * from sys.sysservers

sys.linked_logins 查看登入的登陸訪問帳戶(即查看使用sp_addlinkedsrvlogin登入過的帳戶) select * from sys.linked_logins

sys.remote_logins 查看登入的遠端訪問帳戶 select * from sys.remote_logins