最有效的方法基于结果的完全不同的数据库中设置一个数据库中选择记录 [英] Most efficient way to select records in one database based on result set from totally different database
问题描述
我有两个完全不同的数据库 - 一个MSSQL和一个普及。由于我们产品的数据存储在数据库中普及的方式，你不能轻易运行查询来获取产品信息和功能显示在我们的网站。
I have 2 totally separate databases - one MSSQL and one Pervasive. Due to the way our product data is stored in the Pervasive database, you cannot easily run a query to get a products's information and features to display on our website.
因此，使用DTS包我从普及并对其进行处理的产品数据，所以它的产品编号（主键两个数据库）和所有列的产品功能之一MSSQL表。非常容易和快速查询我们的网站。
So, using a DTS package I take the product data from Pervasive and process it so it is one MSSQL table with the product item # (primary key for both databases) and all of the columns for the product features. Very easy and fast to query for our website.
普及数据库是由ERP系统之一，所以它始终拥有最先进的最新库存总计为每个产品。现在，我需要找到最有效地拉动从普及基础数据库从MSSQL数据库中检索的实时基础上记录的库存信息的最佳方式。
The Pervasive database is the one used by the ERP system, so it always has the most up-to-date inventory totals for each product. Now, I need to find the best way to most efficiently pull the inventory information from the Pervasive database based on the records retrieved from the MSSQL database on a real-time basis.
获取只有一个产品的库存信息也没什么大不了的。我主要关心的是如何拉清单数据项的列表，从上MSSQL产品表的查询返回，并有库存数据匹配到正确的项目。
Getting just one product's inventory information is no big deal. My main concern is how to pull inventory data for a list of items returned from a query on the MSSQL product table and have the inventory data match up with the correct items.
缓存全部来自普及分贝库存数据将无法工作，因为我需要它是实时的。
Caching all of the inventory data from the Pervasive db won't work because I need it to be real-time.
什么是最有效的选择我拉这一数据，除了生成SELECT语句列表中的每个项目？我想这只是一个数据库调用如果可能的话。
What are the most efficient options for me to pull this data, besides generating SELECT statements for each item in the list? I would like it to only be one database call if possible.
感谢。
推荐答案
为了将数据访问从SQL Server不同的数据库，你必须首先创建的链接的服务器SQL Server实例指向其他数据库（普适在本例中）与相应的凭据/权限的。 这篇文章是用于连接MySQL的到SQL Server 2008 ，但步骤是相同的。
In order to access data to a different database from SQL Server, you'd have to first create a linked server instance on SQL Server pointing to the other db (Pervasive in this example) with appropriate credentials/permissions. This article is for linking MySQL to SQL Server 2008, but the steps are the same.
一旦在地方的，你可以写在SQL Server查询，就好像你在处理一个单一的数据库。
Once that's in place, you can write queries in SQL Server as though you were dealing with a single database.
这篇关于最有效的方法基于结果的完全不同的数据库中设置一个数据库中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
