SQL Server跨库访问

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

SQL Server跨库访问,布布扣,bubuko.com