Sqlserver中使用DBLINK

一、 创建链接服务器

1、使用sp_addlinkedserver来增加链接

EXEC sp_addlinkedserver
@server='192.168.2.66',--被访问的服务器别名（习惯上直接使用目标服务器IP，或取个别名如：JOY）
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.2.66' --要访问的服务器

2、使用sp_addlinkedsrvlogin 来增加用户登录链接

EXEC sp_addlinkedsrvlogin
'192.168.2.66', --被访问的服务器别名（如果上面sp_addlinkedserver中使用别名JOY，则这里也是JOY）
'false',
NULL,
'sa', --帐号
'test123' --密码

3、查看当前链接情况：

select * from sys.servers;

4、使用 sp_helpserver 来显示可用的服务器

Exec sp_helpserver

5、删除已经存在的某个链接

Exec sp_droplinkedsrvlogin 服务器别名,Null
Exec sp_dropserver 服务器别名

EXEC SP_DROPSERVER'ITSV' ,'DROPLOGINS'

可能会遇到的问题：
exec sp_dropserver 'JOY’执行失败，

报错信息：仍有对服务器 ‘JOY’ 的远程登录或链接登录。

解决方法：

exec sp_droplinkedsrvlogin 'JOY',null

exec sp_dropserver 'JOY'

6、查询示例

SELECT * FROM ITSV.数据库名.DBO.表名

举例(访问目标服务器上的数据库Music，查看其中表test的内容)：
如果建立链接时的别名是目标服务器IP，即192.168.2.66 ，则：

select * from [192.168.2.66].[Music].dbo.test

如果建立链接时的别名是JOY, 则：

select * from [JOY].[Music].dbo.test

7、导入示例

SELECT * INTO 表 FROM ITSV.数据库名.DBO.表名

二、连接远程/局域网数据(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

–OPENQUERY用法需要创建一个连接

–首先创建一个连接创建链接服务器

EXEC SP_ADDLINKEDSERVER'ITSV' ,'' , 'SQLOLEDB' ,'远程服务器名或IP地址'

–查询

SELECT * FROM OPENQUERY(ITSV ,'SELECT * FROM 数据库.DBO.表名')

–把本地表导入远程表

 INSERT OPENQUERY(ITSV ,'SELECT * FROM 数据库.DBO.表名')
 SELECT * FROM 本地表

–更新本地表

UPDATE BSET B.列B=A.列B 
				FROM OPENQUERY(ITSV , 'SELECT * FROM 数据库.DBO.表名')AS A 
				INNER JOIN 本地表 BON A.列A=B.列A

–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 本地表