sql server - MS Excel - join external (SQL) data with local table (sheet)

Question

I'm trying to get data from both external source and another sheet of my excel file into one table. What I have in SQL Server is a table of my customers: ╔════╦══════╦═════════╗ ║ ID ║ Name ║ Country ║ ╠════╬══════╬═════════╣ ║ 1 ║ Joe ║ Spain ║ ║ 2 ║ Bob ║ Frence ║ ║ 3 ║ Eva ║ Spain ║ ╚════╩══════╩═════════╝ What I have in another sheet of my excel file is a list of countries and taxes: ╔═════════╦═════╗ ║ Country ║ VAT ║ ╠═════════╬═════╣ ║ Germany ║ 19% ║ ║ Frence ║ 20% ║ ║ Spain ║ 21% ║ ╚═════════╩═════╝ And what I need in the end is a table which contains my customer and the tax which he should pay. The table should be in one of the sheets of my excel document and user should be able to easily update it on demand. ╔══════════╦═════╗ ║ Customer ║ VAT ║ ╠══════════╬═════╣ ║ Joe ║ 21% ║ ║ Bob ║ 20% ║ ║ Eva ║ 21% ║ ╚══════════╩═════╝ Any idea what would be a nice and easy solution?

I tried two excel sql queries - one from SQL Server and second one "from microsoft query" (from the excel file). But since I cannot run them subsequenty, I cannot make sure the data after one update is correct.

I also tried one sql queries (from SQL Server) and a VLOOKUP formula. But the formula is not automatically calculated after the table is filled in.

Answer 1

My comment above was merely intended to open a few possibilities which you might have at hand.

PowerQuery is a free Plug-In for Office 2013. In Office 2016 it is part of Excel and no longer a Plug-In.

Anyway, you seem to prefer the approach to use a temp table or a table variable on your SQL. Hence, I will elaborate a bit more on this approach:

In the end you will want a query similar to this one:

set nocount on;
declare @tblVAT table
    (
     Country nvarchar(50),
     VAT decimal(9, 7)
    );

insert  into @tblVAT
        (Country, VAT)
values  (N'Germany', 0.19),
        (N'Frence', 0.20),
        (N'Spain', 0.21);


select  tc.Name,
        tc.ID,
        case when tc.Country is null then tv.Country
             else tc.Country
        end as Country,
        tv.VAT
from    dbo.tblCustomers as tc
full outer join @tblVAT as tv
on      tv.Country = tc.Country;

_{Note the importance of set nocount on; at the start of the above SQL query. Without that it will not work!}

Once, you have this query you can simply paste it into Excel using the menu Data ? Get External Data ? From SQL Server. In a first step you will get the customer table and then in a second step refine the query to include the table variable as described above. Here is a short visual summary:

I guess, at this point the only remaining questions are:

1. How do you dynamically create the above SQL statement and
2. how do you get the above table in Excel then updated with this updated SQL statement.

To dynamically create the above SQL you might want to have a look at the following: Using an array or dictionary as from clause in sql in excel vba

Just yesterday I answered a similar question where a user wanted to pass the content of an Excel sheet as a dynamically created table to an SQL Server. You can easily adapt (or even use it as is) for your purpose.

For the last step (update this table in Excel with this new SQL query) you can use the macro recorder and do what I did in the above screenshot. The automatically created code is nothing more / less than what I would propose to you.

So, there you have it. Let me know if I wasn't clear enough or if you require further details to understand this solution.