sql server跨库查询

方式一(dbo可以省略)

SELECT
    *
FROM   数据库A.dbo.表A a,
       数据库B.dbo.表B b
WHERE a.field = b.field

方式二(在一个数据库挂上另一个数据库的外链)

//映射一个远程数据库
EXEC sp_addlinkedserver  '远程数据库的IP或主机名',N'SQL Server'

//登录远程数据库
EXEC sp_addlinkedsrvlogin '远程数据库的IP或主机名', 'false', NULL, '登录名', '密码'

//登录后,可以用以下格式操作远程数据库中的对象
SELECT * FROM [远程数据库的IP或主机名].[数据库名].[dbo].[表名]
INSERT INTO OPENROWSET('sqloledb', '192.168.0.100'; 'sa'; '10060', 'select * from knss2009.dbo.yw_kck')
SELECT * FROM yw_kck

//示例:
--创建链接服务器
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
2、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 跨库取数使用示例(注意做判断):

IF EXISTS( SELECT
    1
FROM sys.synonyms
WHERE name = 'SYN305_ys_CostAndCashSet') DROP SYNONYM SYN305_ys_CostAndCashSet
GO
 
CREATE SYNONYM SYN305_ys_CostAndCashSet FOR [dotnet_erp305_hnjy].dbo.ys_CostAndCashSet
GO

 

不允许远程访问出现异常解决方案:

EXEC sp_configure 'show advanced options',1
reconfigure

EXEC sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

 

posted @ 2019-04-15 18:34  WhatAreWords  阅读(10413)  评论(0编辑  收藏  举报