sql server跨库查询方法

未来之歌

方式一（dbo可以省略）

1. SELECT
   
2.     *
   
3. FROM   数据库A.dbo.表A a,
   
4.        数据库B.dbo.表B b
   
5. WHERE a.field = b.field

复制代码

方式二（在一个数据库挂上另一个数据库的外链）

1. //映射一个远程数据库
   
2. EXEC sp_addlinkedserver  '远程数据库的IP或主机名',N'SQL Server'
   
3. 
   
4. //登录远程数据库
   
5. EXEC sp_addlinkedsrvlogin '远程数据库的IP或主机名', 'false', NULL, '登录名', '密码'
   
6. 
   
7. //登录后，可以用以下格式操作远程数据库中的对象
   
8. SELECT * FROM [远程数据库的IP或主机名].[数据库名].[dbo].[表名]
   
9. INSERT INTO OPENROWSET('sqloledb', '192.168.0.100'; 'sa'; '10060', 'select * from knss2009.dbo.yw_kck')
   
10. SELECT * FROM yw_kck
    
11. 
    
12. //示例：
    
13. --创建链接服务器
    
14. EXEC sp_addlinkedserver  'ITSV ',
    
15.                      　　' ',
    
16.                      　　'SQLOLEDB ',
    
17.                      　　'远程服务器名或ip地址'
    
18. EXEC sp_addlinkedsrvlogin   'ITSV ',
    
19.                          　　'false ',
    
20.                          　　NULL,
    
21.                          　　'用户名',
    
22.                          　　'密码'
    
23. 
    
24. //查询示例
    
25. SELECT
    
26.     *
    
27. FROM ITSV.数据库名.dbo.表名
    
28. 
    
29. //导入示例
    
30. SELECT
    
31.     * INTO 表
    
32. FROM ITSV.数据库名.dbo.表名
    
33. 
    
34. //以后不再使用时删除链接服务器
    
35. EXEC sp_dropserver   'ITSV ', 'droplogins '

复制代码

方式三
连接远程/局域网数据(openrowset/openquery/opendatasource)
1、openrowset （比较推荐这种做法）

1. //查询示例
   
2. SELECT
   
3.     *
   
4. FROM OPENROWSET('SQLOLEDB ', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)
   
5. 
   
6. //生成本地表
   
7. SELECT
   
8.     * INTO 表
   
9. FROM OPENROWSET('SQLOLEDB ', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)
   
10. 
    
11. //把本地表导入远程表
    
12. INSERT OPENROWSET('SQLOLEDB ', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)
    
13.     SELECT
    
14.        *
    
15.     FROM 本地表
    
16. 
    
17. //更新本地表
    
18. UPDATE b
    
19. SET b.列A = a.列A
    
20. FROM OPENROWSET('SQLOLEDB ', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名) AS a
    
21. INNER JOIN 本地表 b
    
22.     ON a.column1 = b.column1

复制代码

2、openquery用法需要创建一个连接

1. //首先创建一个连接创建链接服务器
   
2. EXEC sp_addlinkedserver  'ITSV ',
   
3.                      　　 ' ',
   
4.                      　　 'SQLOLEDB ',
   
5.                      　　 '远程服务器名或ip地址'
   
6. 
   
7. //查询
   
8. SELECT * FROM OPENQUERY(ITSV, 'SELECT *  FROM 数据库.dbo.表名')
   
9. 
   
10. //把本地表导入远程表
    
11. INSERT OPENQUERY(ITSV, 'SELECT *  FROM 数据库.dbo.表名') SELECT *  FROM 本地表
    
12. 
    
13. //更新本地表
    
14. UPDATE b
    
15. SET b.列B = a.列B
    
16. FROM OPENQUERY(ITSV, 'SELECT * FROM 数据库.dbo.表名') AS a
    
17. INNER JOIN 本地表 b  ON a.列A = b.列A

复制代码

3、opendatasource/openrowset

1. SELECT
   
2.     *
   
3. FROM OPENDATASOURCE('SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').test.dbo.roy_ta
   
4. 
   
5. //把本地表导入远程表
   
6. INSERT OPENDATASOURCE('SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').数据库.dbo.表名
   
7.     SELECT
   
8.        *
   
9.     FROM 跨库取数使用示例(注意做判断)：
   
10. 
    
11. IF EXISTS( SELECT
    
12. FROM sys.synonyms
    
13. WHERE name = 'SYN305_ys_CostAndCashSet') DROP SYNONYM SYN305_ys_CostAndCashSet
    
14. GO
    
15. 
    
16. CREATE SYNONYM SYN305_ys_CostAndCashSet FOR [dotnet_erp305_hnjy].dbo.ys_CostAndCashSet
    
17. GO

复制代码

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

1. EXEC sp_configure 'show advanced options',1
   
2. reconfigure
   
3. 
   
4. EXEC sp_configure 'Ad Hoc Distributed Queries',1
   
5. reconfigure

复制代码