SQL本地分布式操作远程数据库，可用于实现导入，导出等数据操作功能

 

-- 遠程連接操作
/* *****************************************************************************************************************************************************
Tab表:
/*
ID 自增列         Name
----------- ---------
1           a
2           b
3           c
*/
存儲過程:
/*
create proc P
as
select top 10 ID,Name from sysobjects
*/

******************************************************************************************************************************************************/

-- 遠程鏈接服務器機器名:Roy;實例名:Roy/SQL2005DE;登陸名:sa;密碼:Test2005;

-- 創建鏈接服務器
EXEC master.dbo.sp_addlinkedserver @server = N ' ROY_LNK ' , @srvproduct = N ' ROY_LNK ' , @provider = N ' SQLOLEDB ' , @datasrc = N ' Roy/SQL2005DE ' -- SQL05可用SQLNCLI/SQLOLEDB
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N ' ROY_LNK ' , @locallogin = NULL , @useself = N ' False ' , @rmtuser = N ' sa ' , @rmtpassword = N ' Test2005 '
GO

-- 刪除鏈接服務器
-- exec sp_dropserver 'roy_lnk','droplogins'

-- Select
select * from roy_lnk.test.dbo.Tab

-- insert
insert roy_lnk.test.dbo.Tab(Name) -- 指定列
Values ( ' d ' )

-- update
update roy_lnk.test.dbo.Tab set Name = ' DD ' where Name = ' d '

-- delete
delete roy_lnk.test.dbo.Tab where Name = ' DD '

用openrowset:

-- 支持存儲過程
select * from openrowset ( ' SQLOLEDB ' , ' Roy/SQL2005DE ' ; ' sa ' ; ' Test2005 ' , ' test.dbo.p ' )a


-- select
select * from openrowset ( ' SQLOLEDB ' , ' Roy/SQL2005DE ' ; ' sa ' ; ' Test2005 ' ,test.dbo.Tab)a


-- insert
insert openrowset ( ' SQLOLEDB ' , ' Roy/SQL2005DE ' ; ' sa ' ; ' Test2005 ' ,test.dbo.Tab)(Name) values ( ' EE ' )

-- update
update openrowset ( ' SQLOLEDB ' , ' Roy/SQL2005DE ' ; ' sa ' ; ' Test2005 ' ,test.dbo.Tab) set Name = ' EEE ' where Name = N ' EE '

-- delete
delete openrowset ( ' SQLOLEDB ' , ' Roy/SQL2005DE ' ; ' sa ' ; ' Test2005 ' ,test.dbo.Tab) where Name = N ' EEE '


用openquery: -- 支持存儲過程

-- 支持存儲過程
select *   from openquery (roy_lnk, ' test.dbo.P ' )


-- select
SELECT * FROM openquery (roy_lnk, ' SELECT * FROM test.dbo.tab ' )

-- insert
insert openquery (roy_lnk, ' SELECT * FROM test.dbo.tab ' ) (Name) values ( ' F ' )

-- update
update openquery (roy_lnk, ' SELECT * FROM test.dbo.tab ' ) set Name = ' FF ' where Name = ' F '

-- delete不支持是根據 OLE DB 提供者的功能而定
-- delete openquery(roy_lnk, 'SELECT * FROM test.dbo.tab')  where Name='FF'



用OPENDATASOURCE:

-- 支持存儲過程
select *   from OPENDATASOURCE ( ' SQLOLEDB ' , ' Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005 ' ). ' test.dbo.p '

-- select
select *   from OPENDATASOURCE ( ' SQLOLEDB ' , ' Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005 ' ).test.dbo.tab

-- insert
insert OPENDATASOURCE ( ' SQLOLEDB ' , ' Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005 ' ).test.dbo.tab(Name) values ( ' H ' )

-- update
update OPENDATASOURCE ( ' SQLOLEDB ' , ' Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005 ' ).test.dbo.tab set Name = ' HH ' where Name = ' H '

-- delete
delete OPENDATASOURCE ( ' SQLOLEDB ' , ' Data Source=Roy/SQL2005DE;User ID=sa;Password=Test2005 ' ).test.dbo.tab where Name = ' HH '


-- 與本機表數據對比時用別名
select * from roy_lnk.test.dbo.Tab a join tab b on a.ID = b.ID

-----------------------------------------------------------------------------------------------------------------------------------------

不同服务器数据库之间的数据操作

--创建链接服务器
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 '

--连接远程/局域网数据(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 b
set b.列B=a.列B
FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a 
inner join 本地表 b on 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