I’ve often wondered if I could have a select statement that retrieved data from two databases at a time. I just got a SQL Server tip in the mail this morning that walks you through the steps. It’s awesome! Does everything and more. The keyword is OPENROWSET.
Here’s the link:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1353663_mem1,00.html
Example from above article:
SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle
FROM OPENROWSET(
'SQLNCLI',
'Server=SqlSrv1;Trusted_Connection=yes;',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM AdventureWorks.HumanResources.vEmployee'
) AS e1
INNER JOIN OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Data\Employees.mdb'; 'admin';' ',
'SELECT EmployeeID, FirstName, LastName, JobTitle
FROM Employees'
) AS e2
ON e1.EmployeeID = e2.EmployeeID
ORDER BY e2.LastName, e2.FirstName
Here’s the MSDN link on OPENROWSET.
http://msdn.microsoft.com/en-us/library/ms190312.aspx