MS Excel – join external (SQL) data with local table (sheet)

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.

  • SQL Server : CASE date 30 days before enddate
  • There are about 70 million inserts in a week.What indexes should i use on my columns?
  • When is it better to store flags as a bitmask rather than using an associative table?
  • Get all parents for a child
  • Writing pre/post-deployment scripts for column add in SSDT on table referenced via foreign keys
  • Can i use Pivot for this?
  • 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.

  • Why are system views duplicated to user databases?
  • Left Join with multiple conditions
  • bcp Utility write to remote server?
  • Right click script alter table disabled in SQL Server Management Studio
  • Using SQL Server and mongodb with Repository pattern in node.js
  • SQL Server - Recursion starting from a specific tuple
  • One Solution collect form web for “MS Excel – join external (SQL) data with local table (sheet)”

    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 DataGet External DataFrom 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:

    enter image description here

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.