如何从sql语句或存储过程连接到外部数据库?

When running a SQL statement or a stored procedure on a database, can you connect to an external database and pull data from there?

在数据库上运行SQL语句或存储过程时，能否连接到外部数据库并从中提取数据?

something like:

喜欢的东西:

SELECT a.UserID, b.Da*tToGet 
  FROM mydb.Users as a, externaldb.Data as b

3 个解决方案

#1

You'll need to setup a Linked Server instance. Then you can reference the external database as though it were a SQL Server database.

您需要设置一个链接服务器实例。然后可以引用外部数据库，就好像它是一个SQL Server数据库一样。

#2

Yep -- there's two methods: either use the function OPENROWSET, or use linked servers. OPENROWSET is useful for ad-hoc single statements, but if you're going to be doing this regularly, read up on linked servers as they allow you to do exactly what you've specified in your SQL Statement ... e.g.,

有两种方法:要么使用函数OPENROWSET，要么使用链接服务器。OPENROWSET对于特定的单个语句非常有用，但是如果您打算经常这样做，请阅读链接服务器，因为它们允许您执行SQL语句中指定的操作……例如,

SELECT database.owner.table for local data
SELECT server.database.owner.table for remote data

选择database.owner。本地数据的表选择server.database.owner。表为远程数据

And yes, you can mix and match to do joins twixt local and remote. Note though that you'll need to be caureul if you do joins against large tables that exist on the remote server as the query could take a long time to exexute...

是的，您可以混合和匹配来进行本地和远程连接。请注意，如果您对远程服务器上存在的大型表进行连接，则需要使用caureul，因为查询可能需要很长时间才能完成……

#3

Yes, you can. You should take a look at linked servers for starters. You can also use OPENROWSET to hit them directly with no linked server.

是的,你可以。首先，您应该查看一下链接服务器。您还可以使用OPENROWSET直接在没有链接服务器的情况下攻击它们。

#1