Home > PowerPivot > Write SQL Query using Table Import Wizard in PowerPivot

Write SQL Query using Table Import Wizard in PowerPivot


Query builder helps us to design SQL queries with simple steps and it is less time-consuming.

We also have Query designer in Excel PowerPivot that helps user to design SQL queries to import data easily. Table import wizard query designer is slightly different from the SSMS query designer. But, it does the samething.  In this article, I will explain the steps involved in building  a SQL query with a simple join clause

We have got two tables for this example

  1. tblEmployee
  • Empid int
  • EmpName varchar(50)
  • Deptid int
  1. tblDepartment
  • Deptid int
  • DeptName varchar(50)

Implementation

Step 1: Open Excel and Click PowerPivot Window

Step 2: Now, Connect to the SQL Server Database and fill connection string and click next

Step 2: Select write a query….. Option in the Table Import Wizard and click next and press the design button

Step 3: Select required tables from the tables list and Deselect Auto Detect button

Step 4: Now expand the Relationship tab and Add new relationship

Step 5:  Add relationship tables for left and rigth side here

Step 6: Add Join key fields from left and right table

Step Click Ok and complete the design

Data will be populated in to the PowerPivot window after you click finish and close button in the Table Import Wizard.

Thanks for reading

Advertisements
  1. March 4, 2014 at 11:11 am

    hi, is ti posible to update the querie, i have allready created the querie and now i need to add a new field to the queryy, is that posible?

    • March 5, 2014 at 11:25 am

      Yes, it is possible. the metadata should be updated.

      • Diane
        April 22, 2015 at 7:28 am

        Dumb question, I cannot find how to edit an existing import query, Design -> existing connection -> edit only allows editing the connection, and Open only allows creation of a new query. Appreciate any guidance.

  2. Diane
    April 22, 2015 at 7:40 am

    And… for the benefit of any others who stumble upon this page, I found out how to edit the query. Design -> Table Properties -> Design. Thank you for the very useful post.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: