频道栏目
IT货架 > > 正文
SQL不同服务器数据库之间的数据操作整理(完整版)(转) 语法 参数 备注 权限 示例 语法 参数 备注 权限 示例 语法 参数 备注 权限 示例
网友分享于:Jun 12, 2018 11:46:29 PM    来源: IT货架   

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

-- Author : htl258(Tony)

-- Date   : 2010-06-25 22:23:18

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258(转载保留此信息)

-- Subject: SQL不同服务器数据库之间数据操作整理

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

--1. 创建链接服务器

--1.1 创建一个链接名

EXEC sp_addlinkedserver 'LinkName','','SQLOLEDB','远程服务器名或ip地址' --有自定义实例名还要加上"/实例名"

    /*例如:EXEC sp_addlinkedserver 'TonyLink','','SQLOLEDB','192.168.58.208' */  

--1.2 创建登录信息(或叫创建链接服务器登录名映射)(只需选择一种方式)

--1.2.1 以windows认证的方式登录

EXEC sp_addlinkedsrvlogin 'LinkName'  --或EXEC sp_addlinkedsrvlogin 'LinkName','true'

    /*例如:EXEC sp_addlinkedsrvlogin 'TonyLink'  */  

--1.2.2 以SQL认证的方式登录                                                                    

EXEC sp_addlinkedsrvlogin 'LinkName','false',NULL,'用户名','密码'

    /*例如:EXEC sp_addlinkedsrvlogin 'TonyLink','false',null,'sa','123' */

 

--2. 链接服务器相关数据操作

--2.1 查询示例

SELECT * FROM LinkName.数据库名.架构名.表名

    /*例如:SELECT * FROM TonyLink.Mydb.dbo.tb */

--2.2 导入示例

SELECT * INTO 表名 FROM LinkName.数据库名.架构名.表名

    /*例如:SELECT * INTO Newtb FROM TonyLink.Mydb.dbo.tb */

--2.3 更新示例

UPDATE LinkName.数据库名.架构名.表名 SET 字段='值' WHERE 字段='条件'

    /*例如:UPDATE TonyLink.Mydb.dbo.tb SET Persons='g' WHERE Persons='a' */

--2.4 删除示例

DELETE LinkName.数据库名.架构名.表名 WHERE 字段名='条件'

    /*例如:DELETE TonyLink.Mydb.dbo.tb WHERE Persons='g' */

 

--3. 通过行集函数(OPENQUERY/OPENROWSET/OPENDATASOURCE)操作方法

--3.1 OPENQUERY 方法(需要借助刚创建的链接服务器):

--3.1.1 查询示例

SELECT * FROM OPENQUERY(LinkName,'SELECT * FROM 数据库名.架构名.表名')

    /* 例如:SELECT * FROM OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb')  */

--3.1.2 导入示例

--3.1.2.1 导入所有列

INSERT OPENQUERY(LinkName, 'SELECT *  FROM 数据库名.架构名.表名') SELECT * FROM 本地表

    /* 例如:INSERT OPENQUERY(TonyLink,'SELECT *  FROM Mydb.dbo.tb') SELECT * FROM tb */

--3.1.2.2 导入指定列

INSERT OPENQUERY(LinkName, 'SELECT *  FROM 数据库名.架构名.表名') (列,列...)

    SELECT 列,列... FROM 本地表

    /* 例如:INSERT OPENQUERY(TonyLink,'SELECT *  FROM Mydb.dbo.tb')(RANGE,LEVEL,Persons)

           SELECT RANGE,LEVEL,Persons FROM tb

    */

--3.1.3 更新示例

UPDATE OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名')  SET 字段='值' WHERE 字段='条件'

    /*例如:UPDATE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb')  SET Persons='g' WHERE Persons='a' */

--3.1.4 删除示例

DELETE OPENQUERY(LinkName, 'SELECT * FROM 数据库名.架构名.表名')  WHERE 字段名='条件'

    /*例如:DELETE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb')  WHERE Persons='g' */

 

--3.2 OPENROWSET方法(不需要用到创建好的链接名。如果连接的实例名不是默认的,需要在"sql服务器名或IP地址"后加上"/实例名")

--3.2.1 查询示例

--3.2.1.1 Windows认证方式查询(以下方法之一即可)

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 * FROM OPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)

         或:SELECT * FROM OPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes',Mydb.dbo.tb)

    或:SELECT * FROM OPENROWSET('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes','SELECT * FROM Mydb.dbo.tb') 

    或:SELECT * FROM OPENROWSET('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes','SELECT * FROM Mydb.dbo.tb')

    */

--3.2.1.2 SQL认证方式查询(以下方法之一即可)

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 * FROM OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb) 

        或:SELECT * FROM OPENROWSET('SQLNCLI','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb) 

        或:SELECT * FROM OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123','SELECT * FROM Mydb.dbo.tb') 

        或:SELECT * FROM OPENROWSET('SQLNCLI','server=192.168.58.208;uid=sa;pwd=123','SELECT * FROM Mydb.dbo.tb')

        或:SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123',mydb.dbo.tb) 

        或:SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208';'sa';'123',mydb.dbo.tb) 

        或:SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208';'sa';'123','SELECT * FROM Mydb.dbo.tb') 

        或:SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208';'sa';'123','SELECT * FROM Mydb.dbo.tb') 

    */

--3.2.2 导入示例

--3.2.2.1 导入所有列

INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)

SELECT * FROM 本地表

    /* 例如:INSERT OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)

            SELECT * FROM tb

    */

--3.2.2.2 导入指定列

INSERT OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)(列,列...)

SELECT 列,列... FROM 本地表

    /* 例如:INSERT OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)(RANGE,LEVEL,Persons)

            SELECT RANGE,LEVEL,Persons FROM tb

    */

--注:更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。

--3.2.3 更新示例

UPDATE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名)

SET 字段='值'

WHERE 字段='条件'

    /*例如:UPDATE OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb)

            SET Persons='g'

             WHERE Persons='a'

    */

--注:更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。

--3.2.4 删除示例

DELETE OPENROWSET('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码',数据库名.架构名.表名) 

WHERE 字段名='条件'

    /*例如:DELETE OPENROWSET('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123',mydb.dbo.tb) 

            WHERE Persons='g'

    */

--注:更多替代方法参考.2.1查询示例,只需替换行集函数(OPENROWSET)内的内容即可。

 

--3.3 OPENDATASOURCE方法(不需要用到创建好的链接名。如果连接的实例名不是默认的,需要在"sql服务器名或IP地址"后加上"/实例名")

--3.3.1 查询示例

--3.3.1.1 Windows认证方式查询(以下方法之一即可)

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;Trusted_Connection=yes').数据库名.架构名.表名

    /* 例如:SELECT * FROM OPENDATASOURCE('SQLOLEDB','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb

         或:SELECT * FROM OPENDATASOURCE('SQLNCLI','Server=192.168.58.208;Trusted_Connection=yes').Mydb.dbo.tb

    */

--3.3.1.2 SQL认证方式查询(以下方法之一即可)

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

    /* 例如:SELECT * FROM OPENDATASOURCE('SQLOLEDB','Server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb 

        或:SELECT * FROM OPENDATASOURCE('SQLNCLI','Server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb

        或:SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb

        或:SELECT * FROM OPENDATASOURCE('SQLNCLI','Data source=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 本地表

    /* 例如:INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb

            SELECT * FROM tb

    */

--3.3.2.2 导入指定列

INSERT OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名(列,列...)

SELECT 列,列... FROM 本地表

    /* 例如:INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb(RANGE,LEVEL,Persons)

            SELECT RANGE,LEVEL,Persons FROM tb

    */

--注:更多替代方法参考.3.1查询示例,只需替换行集函数(OPENDATASOURCE)内的内容即可。

--3.3.3 更新示例

UPDATE OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

SET 字段='值'

WHERE 字段='条件'

    /*例如:UPDATE OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb

            SET Persons='g'

             WHERE Persons='a'

    */

--注:更多替代方法参考.3.1查询示例,只需替换行集函数(OPENDATASOURCE)内的内容即可。

--3.3.4 删除示例

DELETE OPENDATASOURCE('SQLOLEDB', 'server=sql服务器名或IP地址;uid=用户名;pwd=密码').数据库名.架构名.表名

WHERE 字段名='条件'

    /*例如:DELETE OPENDATASOURCE('SQLOLEDB','server=192.168.58.208;uid=sa;pwd=123').mydb.dbo.tb

            WHERE Persons='g'

    */

--注:更多替代方法参考.3.1查询示例,只需替换行集函数(OPENDATASOURCE)内的内容即可。

 

--4. 删除链接服务器方法

--如果以后不再使用时可删除链接信息

--4.1 删除登录信息(或叫删除链接服务器登录名映射)

EXEC sp_droplinkedsrvlogin 'LinkName',NULL

    /*例如:EXEC sp_droplinkedsrvlogin 'TonyLink',NULL  */

--4.2 删除链接服务器名称

EXEC sp_dropserver 'LinkName','droplogins' --如果指定droplogins,则在删除链接服务器之前要删除登录名映射

    /*例如:EXEC sp_dropserver 'TonyLink','droplogins' */

 

 

 

--附:获取Provider Name的方法(EXEC master..xp_enum_oledb_providers)

SELECT CAST([Provider Name] AS VARCHAR(30)) ProviderName,

       CAST([Provider Description] AS VARCHAR(60)) ProviderDescription

FROM OPENROWSET(

    'SQLOLEDB',

    'Server=.;Trusted_Connection=yes',

    'SET FMTONLY OFF;

     EXEC master..xp_enum_oledb_providers'

    )

/*

ProviderName                   ProviderDescription

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

SQLOLEDB                       Microsoft OLE DB Provider for SQL Server

DTSPackageDSO                  Microsoft OLE DB Provider for DTS Packages

SQLReplication.OLEDB           SQL Server Replication OLE DB Provider for DTS

MSOLAP                         Microsoft OLE DB Provider for Analysis Services 10.0

MSDMine                        Microsoft OLE DB Provider For Data Mining Services

Microsoft.ACE.OLEDB.12.0       Microsoft Office 12.0 Access Database Engine OLE DB Provider

ADsDSOObject                   OLE DB Provider for Microsoft Directory Services

SQLNCLI10                      SQL Server Native Client 10.0

MSDAIPP.DSO                    Microsoft OLE DB Provider for Internet Publishing

MSDASQL                        Microsoft OLE DB Provider for ODBC Drivers

Microsoft.Jet.OLEDB.4.0        Microsoft Jet 4.0 OLE DB Provider

MSDAOSP                        Microsoft OLE DB Simple Provider

MSDAORA                        Microsoft OLE DB Provider for Oracle

MSIDXS                         Microsoft OLE DB Provider for Indexing Service

 

(14 行受影响)

*/ 

 

 

 

OpenRowSet相关帮助:

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

    包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时,这种方法是一种替代方法,并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。对于较频繁引用 OLE DB 数据源的情况,请改为使用链接服务器。有关详细信息,请参阅 链接服务器。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 访问接口的功能,还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集,但 OPENROWSET 只返回第一个结果集。

    OPENROWSET 还通过内置的 BULK 访问接口支持大容量操作,正是有了该访问接口,才能从文件读取数据并将数据作为行集返回。

 

 Transact-SQL 语法约定

语法

参数

备注

权限

示例


广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1