SQL不同服务器数据库之间的数据操作整理完整版.docx
《SQL不同服务器数据库之间的数据操作整理完整版.docx》由会员分享，可在线阅读，更多相关《SQL不同服务器数据库之间的数据操作整理完整版.docx（28页珍藏版）》请在冰点文库上搜索。
SQL不同服务器数据库之间的数据操作整理完整版
SQL不同服务器数据库之间的数据操作整理（完整版）
--1. 创建链接服务器
--1.1 创建一个链接名
EXEC sp_addlinkedserver 'LinkName','','SQLOLEDB','远程服务器名或ip地址' --有自定义实例名还要加上"/实例名"
/*例如：
EXECsp_addlinkedserver'TonyLink','','SQLOLEDB','192.168.58.208'*/
--1.2 创建登录信息（或叫创建链接服务器登录名映射）（只需选择一种方式）
--1.2.1 以windows认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName' --或EXECsp_addlinkedsrvlogin'LinkName','true'
/*例如：
EXECsp_addlinkedsrvlogin'TonyLink' */
--1.2.2 以SQL认证的方式登录
EXEC sp_addlinkedsrvlogin 'LinkName','false',NULL,'用户名','密码'
/*例如：
EXECsp_addlinkedsrvlogin'TonyLink','false',null,'sa','123'*/
--2. 链接服务器相关数据操作
--2.1 查询示例
SELECT * FROM LinkName.数据库名.架构名.表名
/*例如：
SELECT*FROMTonyLink.Mydb.dbo.tb*/
--2.2 导入示例
SELECT * INTO 表名 FROM LinkName.数据库名.架构名.表名
/*例如：
SELECT*INTONewtbFROMTonyLink.Mydb.dbo.tb*/
--2.3 更新示例
UPDATE LinkName.数据库名.架构名.表名 SET 字段='值' WHERE 字段='条件'
/*例如：
UPDATETonyLink.Mydb.dbo.tbSETPersons='g'WHEREPersons='a'*/
--2.4 删除示例
DELETE LinkName.数据库名.架构名.表名 WHERE 字段名='条件'
/*例如：
DELETETonyLink.Mydb.dbo.tbWHEREPersons='g'*/
--3. 通过行集函数（OPENQUERY/OPENROWSET/OPENDATASOURCE）操作方法
--3.1OPENQUERY 方法（需要借助刚创建的链接服务器）：
--3.1.1 查询示例
SELECT * FROM OPENQUERY（LinkName,'SELECT*FROM 数据库名.架构名.表名'）
/* 例如：
SELECT*FROMOPENQUERY（TonyLink,'SELECT*FROMMydb.dbo.tb'） */
--3.1.2 导入示例
--3.1.2.1 导入所有列
INSERT OPENQUERY（LinkName, 'SELECT* FROM 数据库名.架构名.表名'） SELECT * FROM 本地表
/* 例如：
INSERTOPENQUERY（TonyLink,'SELECT* FROMMydb.dbo.tb'）SELECT*FROMtb*/
--3.1.2.2 导入指定列
INSERT OPENQUERY（LinkName, 'SELECT* FROM 数据库名.架构名.表名'） （列,列...）
SELECT 列,列... FROM 本地表
/* 例如：
INSERTOPENQUERY（TonyLink,'SELECT* FROMMydb.dbo.tb'）（RANGE,LEVEL,Persons）
SELECTRANGE,LEVEL,PersonsFROMtb
*/
--3.1.3 更新示例
UPDATE OPENQUERY（LinkName, 'SELECT*FROM 数据库名.架构名.表名'） SET 字段='值' WHERE 字段='条件'
/*例如：
UPDATEOPENQUERY（TonyLink,'SELECT*FROMMydb.dbo.tb'） SETPersons='g'WHEREPersons='a'*/
--3.1.4 删除示例
DELETE OPENQUERY（LinkName, 'SELECT*FROM 数据库名.架构名.表名'） WHERE 字段名='条件'
/*例如：
DELETEOPENQUERY（TonyLink,'SELECT*FROMMydb.dbo.tb'） WHEREPersons='g'*/
--3.2OPENROWSET方法（不需要用到创建好的链接名。
如果连接的实例名不是默认的，需要在"sql服务器名或IP地址"后加上"/实例名"）
--3.2.1 查询示例
--3.2.1.1Windows认证方式查询（以下方法之一即可）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes','SELECT*FROM 数据库名.架构名.表名'）
SELECT * FROM OPENROWSET（'SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes','SELECT*FROM 数据库名.架构名.表名'）
/* 例如：
SELECT*FROMOPENROWSET（'SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes','SELECT*FROMMydb.dbo.tb'）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes','SELECT*FROMMydb.dbo.tb'）
*/
--3.2.1.2SQL认证方式查询（以下方法之一即可）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码','SELECT*FROM 数据库名.架构名.表名'）
SELECT * FROM OPENROWSET（'SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码','SELECT*FROM 数据库名.架构名.表名'）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'sql服务器名';'用户名'; '密码',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLNCLI', 'sql服务器名';'用户名'; '密码',数据库名.架构名.表名）
SELECT * FROM OPENROWSET（'SQLOLEDB', 'sql服务器名';'用户名'; '密码','SELECT*FROM 数据库名.架构名.表名'）
SELECT * FROM OPENROWSET（'SQLNCLI', 'sql服务器名';'用户名'; '密码','SELECT*FROM 数据库名.架构名.表名'）
/* 例如：
SELECT*FROMOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123','SELECT*FROMMydb.dbo.tb'）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','server=192.168.58.208;uid=sa;pwd=123','SELECT*FROMMydb.dbo.tb'）
或：
SELECT*FROMOPENROWSET（'SQLOLEDB','192.168.58.208';'sa';'123',mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','192.168.58.208';'sa';'123',mydb.dbo.tb）
或：
SELECT*FROMOPENROWSET（'SQLOLEDB','192.168.58.208';'sa';'123','SELECT*FROMMydb.dbo.tb'）
或：
SELECT*FROMOPENROWSET（'SQLNCLI','192.168.58.208';'sa';'123','SELECT*FROMMydb.dbo.tb'）
*/
--3.2.2 导入示例
--3.2.2.1 导入所有列
INSERT OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）
SELECT * FROM 本地表
/* 例如：
INSERTOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）
SELECT*FROMtb
*/
--3.2.2.2 导入指定列
INSERT OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）（列,列...）
SELECT 列,列... FROM 本地表
/* 例如：
INSERTOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）（RANGE,LEVEL,Persons）
SELECTRANGE,LEVEL,PersonsFROMtb
*/
--注：
更多替代方法参考.2.1查询示例，只需替换行集函数（OPENROWSET）内的内容即可。
--3.2.3 更新示例
UPDATE OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）
SET 字段='值'
WHERE 字段='条件'
/*例如：
UPDATEOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）
SETPersons='g'
WHEREPersons='a'
*/
--注：
更多替代方法参考.2.1查询示例，只需替换行集函数（OPENROWSET）内的内容即可。
--3.2.4 删除示例
DELETE OPENROWSET（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名）
WHERE 字段名='条件'
/*例如：
DELETEOPENROWSET（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb）
WHEREPersons='g'
*/
--注：
更多替代方法参考.2.1查询示例，只需替换行集函数（OPENROWSET）内的内容即可。
--3.3OPENDATASOURCE方法（不需要用到创建好的链接名。
如果连接的实例名不是默认的，需要在"sql服务器名或IP地址"后加上"/实例名"）
--3.3.1 查询示例
--3.3.1.1Windows认证方式查询（以下方法之一即可）
SELECT * FROM OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes'）.数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE（'SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes'）.数据库名.架构名.表名
/* 例如：
SELECT*FROMOPENDATASOURCE（'SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes'）.Mydb.dbo.tb
或：
SELECT*FROMOPENDATASOURCE（'SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes'）.Mydb.dbo.tb
*/
--3.3.1.2SQL认证方式查询（以下方法之一即可）
SELECT * FROM OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE（'SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE（'SQLOLEDB', 'DataSource=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
SELECT * FROM OPENDATASOURCE（'SQLNCLI', 'DataSource=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
/* 例如：
SELECT*FROMOPENDATASOURCE（'SQLOLEDB','Server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
或：
SELECT*FROMOPENDATASOURCE（'SQLNCLI','Server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
或：
SELECT*FROMOPENDATASOURCE（'SQLOLEDB','DataSource=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
或：
SELECT*FROMOPENDATASOURCE（'SQLNCLI','Datasource=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
*/
--3.3.2 导入示例
--3.3.2.1 导入所有列
INSERT OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
SELECT * FROM 本地表
/* 例如：
INSERTOPENDATASOURCE（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
SELECT*FROMtb
*/
--3.3.2.2 导入指定列
INSERT OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名（列,列...）
SELECT 列,列... FROM 本地表
/* 例如：
INSERTOPENDATASOURCE（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb（RANGE,LEVEL,Persons）
SELECTRANGE,LEVEL,PersonsFROMtb
*/
--注：
更多替代方法参考.3.1查询示例，只需替换行集函数（OPENDATASOURCE）内的内容即可。
--3.3.3 更新示例
UPDATE OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
SET 字段='值'
WHERE 字段='条件'
/*例如：
UPDATEOPENDATASOURCE（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
SETPersons='g'
WHEREPersons='a'
*/
--注：
更多替代方法参考.3.1查询示例，只需替换行集函数（OPENDATASOURCE）内的内容即可。
--3.3.4 删除示例
DELETE OPENDATASOURCE（'SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码'）.数据库名.架构名.表名
WHERE 字段名='条件'
/*例如：
DELETEOPENDATASOURCE（'SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123'）.mydb.dbo.tb
WHEREPersons='g'
*/
--注：
更多替代方法参考.3.1查询示例，只需替换行集函数（OPENDATASOURCE）内的内容即可。
--4. 删除链接服务器方法
--如果以后不再使用时可删除链接信息
--4.1 删除登录信息（或叫删除链接服务器登录名映射）
EXEC sp_droplinkedsrvlogin 'LinkName',NULL
/*例如：
EXECsp_droplinkedsrvlogin'TonyLink',NULL */
--4.2 删除链接服务器名称
EXEC sp_dropserver 'LinkName','droplogins' --如果指定droplogins，则在删除链接服务器之前要删除登录名映射
/*例如：
EXECsp_dropserver'TonyLink','droplogins'*/
--附：
获取ProviderName的方法（EXECmaster..xp_enum_oledb_providers）
SELECT CAST（[ProviderName] AS VARCHAR（30）） ProviderName,
CAST（[ProviderDescription] AS VARCHAR（60）） ProviderDescription
FROM OPENROWSET（
'SQLOLEDB',
'Server=.;Trusted_Connection=yes',
'SETFMTONLYOFF;
EXECmaster..xp_enum_oledb_providers'
）
/*
ProviderName ProviderDescription
------------------------------------------------------------------------------------------
SQLOLEDB MicrosoftOLEDBProviderforSQLServer
DTSPackageDSO MicrosoftOLEDBProviderforDTSPackages
SQLReplication.OLEDB SQLServerReplicationOLEDBProviderforDTS
MSOLAP MicrosoftOLEDBProviderforAnalysisServices10.0
MSDMine MicrosoftOLEDBProviderForDataMiningServices
Microsoft.ACE.OLEDB.12.0 MicrosoftOffice12.0AccessDatabaseEngineOLEDBPr
升级会员 