CODEWITHSUNDEEP
sqlsql-server-2012
jiroe
jiroe

Reputation: 63

Query data in Excel file with SQL Server table

I am trying to do a report for order number which are been refunded, I have a list of them in Excel, but I want to know more about that orders like amount, reason for refund and so on. All that information is stored in a SQL Server table.

It is lot of laborious work to do one by one, is there a way so I can check them in one go? For now I am using where clause but it is taking ages to check one by one.

Please help.

Upvotes: 0

Views: 230

Answers (1)

Richard Hansell
Richard Hansell

Reputation: 5403

Seeing as you haven't provided any data model, etc. I have made some assumptions here. One way to do this would be to create a temporary table, populate it, and then join this to the tables you want to query.

The first step is to create your temporary table, so something like this:

CREATE TABLE #temp (order_number INT);

(Which assumes that you have a unique id for each order number.)

Then you need to populate this table, and how you do this depends very much on how many order numbers are in your Excel workbook. You could write an SSIS package (but this would mean materialising your temporary table first), use the export wizard, etc.

One simple method is to write an Excel formula into a new column for each row in your list, then just copy this down to the bottom, copy and paste it into SSMS and execute the script you generated. You might end up with something like this as your formula:

CONCATENATE("INSERT INTO #temp SELECT ", A1, ";")

Once you have your data in a temporary table, or maybe even a physical table, you can take your queries and JOIN them to this, e.g.:

SELECT o.* FROM MyOrders o INNER JOIN #temp t ON t.order_number = o.order_number;
SELECT r.* FROM MyRefunds r INNER JOIN #temp t ON t.order_number = r.order_number;

Upvotes: 1

Related Questions