Here is my personal favorite way to quickly access data in Excel files. If you want an intuitive way to add data to a table from an .xls file, or simply query the file as if it were a table, select from opendatasource(...) is my personal favorite easy way to do so.
In my example, using SQL 2008 Server Management Studio, I right-clicked on a grouping of registered servers and selected new query (a great new feature of SQL 2008). This opens a query analyzer window with a pink bar at the bottom indicating that multiple servers are about to be queried. Use this feature with caution though, especially in mixed SQL 2000 and 2008 environments, as well as production environments, as not all queries are compatible across all servers.
In this query window, I executed exec sp_validatelogins which gave me a list of all potential orphaned logins along with the server associated with it. After copying the data to a spreadsheet, I had two columns, Server and Orphaned Logins, and saved it as c:\temp\invalidlogins.xls
To query that spreadsheet, keep the middle two lines the same as in the script provided, except for a) changing the path of the file and b) selecting the appropriate sheet name.
You can also use this method to join two queries on the spreadsheet. If you had data on Sheet1 and Sheet2, you could do a join on the two as if they were tables. As for how OPENDATASOURCE works on massive amounts of data as compared to BCP, you may still want to consider using BCP to do so, but for the flexibility of querying a spreadsheet or two, this way of using OPENDATASOURCE is one option to consider.
Gaby Abed
