I created a link server using SQL Server 2008 R2 to an Excel File.
My first question is, is it possible to have all your tables on a single link server? Like creating multiple data sources for a link server or do you have to create an individual Link Server for each Excel file (external data)?
Finally, even when I create multiple Link Server, how do I query all the tables or use a JOIN so that I can query multiple tables?
I cannot answer your first question, having no experience with linked servers into Excel documents.
To join across multiple lined servers, the tripe dot notation is usually easier than using OPENQUERY()
.
SELECT
t1.col1,
t1.col2,
t2.col3
FROM LINKEDSERVER1...table1 t1 JOIN LINKEDSERVER2...table2 t2 ON t1.col1 = t2.col1