Apr
21
2009
0

MSSQL: Joining another server in a select statment

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

Written by Tom in: SQL Server | Tags:

Powered by WordPress | Aeros Theme | TheBuckmaker.com WordPress Themes