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?

Answers


Need Your Help

Translating date objects

ruby-on-rails ruby-on-rails-3.2

In the controller I have something like this:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.