Using Excel Columns in SQL Query for Where Clause

Question

SELECT        
    Table1.SourceName, Table2.TableName, Table3.SourceSQL, 
    Table1.SourcePath, Table4.ProcessType
FROM
    Table1 
INNER JOIN
    Table3 ON Table1.SourceID = Table3.SourceID 
INNER JOIN
    Table2 ON Table3.ID = Table2.ID 
INNER JOIN
    Table4 ON Table1.ProcessID = Table4.ProcessID
WHERE        
    Table2.TableName = '[EXCEL COLUMN 1 ROW 1]' 
    OR Table2.TableName = '[EXCEL COLUMN 1 ROW 2]' 
    OR Table2.TableName = '[EXCEL COLUMN 1 ROW 3]' 
    ...OR Table2.TableName = '[EXCEL COLUMN 1 ROW N]';

The query itself is not my question - there may be some inconsistencies because I altered table/row names in the Select and Join statements.

I'm focused on the Where clause - is there a way I can pull records from an excel spreadsheet (records all in the same column) into this query without writing each one in manually. I'm using MS SQL Server Management Studio 2014.

Let me know if more information is needed.

You can't use SSMS to "query" an Excel sheet so what are you asking? Did you mean to ask about the Import Wizard? Or are you using OPENROWSET? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 28, 2016 at 14:04
I mean: Is there a way to mount this excel file into SQL server manager to pull all the rows from the first column into my query? I'm not sure if it is possible - would import serve that function? I don't want to create a table. — jacobherrington
– jacobherrington, Commented Jun 28, 2016 at 14:05
Why don't you want to create a table? In fact, why do this on SQL Server when PivotTable offers columnstore functionality on the client? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 28, 2016 at 14:06
This is for the purpose of pulling info from an existing DB to create a report. — jacobherrington
– jacobherrington, Commented Jun 28, 2016 at 14:08

Sam · Accepted Answer · 2016-06-28 16:29:53Z

1

You could try installing the access database engine from here https://www.microsoft.com/en-us/download/details.aspx?id=13255 and trying to create a linked server to the excel sheet as mentioned here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3019b4ec-fd3e-41c3-8f2e-cd85dc180ef4/sql-2012-64-bit-linked-server-to-excel-2013-64-bit?forum=transactsql

answered Jun 28, 2016 at 16:29

Sam

9301 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jacobherrington Over a year ago

Thanks, I ended up using a PowerShell script to search the .XML steps where the data I needed was stored, but this would have been much better.

Collectives™ on Stack Overflow

Using Excel Columns in SQL Query for Where Clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related