Use Excel data to filter SQL Server query
I'm needing to join 2 different data sets - one in Sql Server (~13M rows weekly) and the other an Excel worksheet (~25k rows).
The large data set is instances of errors occurring, and the smaller set is support calls. There is a customerID field that links the two.
I'm building a report that shows which errors lead to the most support calls, and don't want to bring all ~13M rows down into Excel to then filter based on the customerID field from the Excel worksheet containing the support call data. Besides, I believe this is too much data for Excel to handle anyway.
I'd like to do a simple join in a Excel query to only return errors that resulted in a support call, like:
SELECT errors.CustomerID ,errors.ErrorID ,call.callID FROM errors --SQL Server Error data INNER JOIN [support call worksheet] calls ON errors.CustomerID = calls.CustomerID
Can you do this in Excel? I've seen similar questions that suggest a massive WHERE IN (...) statement but that won't work for me without a lot of string manipulation.
The manual alternative is to load the Excel data into SQL and do my query there, but I'm trying for something automated for my end users.
Is Power Pivot a viable solution here? Can it handle that much data without severe performance issues?