SQL Server 2008 / 64Bit – How to create a query including Excel

We are running SQL Server 2008 on 64bit boxes. We’ve had a request to create a query that has a Where clause that draws data from an Excel table. In pseudo code, the query might look as follows:

Select column1, column2, column3
From MyDatabase.MyTable
Where column4 IN (Select all values from column 'A' in SubIDs.xls whose sheet name is  'SubIDs')

  • Unexpected query results
  • Column name or number of supplied values does not match table definition. Why?
  • select unique rows based on single distinct column
  • How to get the last run job details in SQL
  • Check empty Guid in SQL
  • How to count row in SQL SERVER 2012 using sys.partitions
  • SQL Results data from rows to columns
  • Timeout expired. The timeout period elapsed prior to completion of the operation or the server. The wait operation timed out - strange behavoiur
  • Convert SQL databound datatable cell to string with 2 decimal places, VB.net
  • Azure VPN to on-premises cannot connect SQL Server from azure web app
  • set the result table name in stored procedure
  • How to find first positive value and third consecutive positive values in SQL?
  • One Solution collect form web for “SQL Server 2008 / 64Bit – How to create a query including Excel”

    It depends how often the data in the XLS changes: if it doesn’t change often, then you can load it into a database table and query it directly. SSIS is a common way to automate this, but there’s no 64-bit OLE DB provider for Jet, so you would need to use 32-bit packages:

    http://msdn.microsoft.com/library/ms141766.aspx

    Alternatively, you could create a linked server to the Excel spreadsheet and query it directly. See example E here (the same comment about the OLE DB provider applies):

    http://msdn.microsoft.com/en-us/library/ms190479.aspx

    Personally I would load the data into MSSQL first if possible, it’s just much easier.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.