Results 1 to 8 of 8

Thread: Import multiple tables at once via SQL statement ... ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2009
    Location
    Gothenburg
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Import multiple tables at once via SQL statement ... ?

    Dear All,

    I am the beginner in SQL and PowerPivot and I hope you can help me.

    I need to connect to MS Navision SQL database and import few tables with various columns selection in each of them. In table Import Wizard I have two options:

    1) Select from a list of tables and views to choose the data to import
    2) Write a query that will specify the data to import

    I want to avoid option 1) as it takes a lot of time to manually find the tables and select needed columns.
    I have written a SQL query but when I paste it to SQL Statement window only 1st table gets imported and the 2nd is omitted. I guess I need to change something in the SQL query but I do not know what. My goal is to upload for example 2 tables to separate 2 tabs in PowerPivot. Please advice how to fix this, thank you.

    Code:
    SELECT 
    [db].[company name prod$cust_ ledger entry].[entry no_], 
    [db].[company name prod$cust_ ledger entry].[customer no_], 
    [db].[company name prod$cust_ ledger entry].[posting date], 
    [db].[company name prod$cust_ ledger entry].[document type], 
    [db].[company name prod$cust_ ledger entry].[document no_], 
    [db].[company name prod$cust_ ledger entry].[description], 
    [db].[company name prod$cust_ ledger entry].[currency code], 
    [db].[company name prod$cust_ ledger entry].[sales (lcy)], 
    [db].[company name prod$cust_ ledger entry].[profit (lcy)], 
    [db].[company name prod$cust_ ledger entry].[sell-to customer no_], 
    [db].[company name prod$cust_ ledger entry].[customer posting group], 
    [db].[company name prod$cust_ ledger entry].[global dimension 1 code], 
    [db].[company name prod$cust_ ledger entry].[global dimension 2 code], 
    [db].[company name prod$cust_ ledger entry].[user id], 
    [db].[company name prod$cust_ ledger entry].[source code], 
    [db].[company name prod$cust_ ledger entry].[due date], 
    [db].[company name prod$cust_ ledger entry].[closed by entry no_], 
    [db].[company name prod$cust_ ledger entry].[closed at date], 
    [db].[company name prod$cust_ ledger entry].[closed by amount], 
    [db].[company name prod$cust_ ledger entry].[bal_ account no_], 
    [db].[company name prod$cust_ ledger entry].[transaction no_], 
    [db].[company name prod$cust_ ledger entry].[document date], 
    [db].[company name prod$cust_ ledger entry].[reversed], 
    [db].[company name prod$cust_ ledger entry].[reversed by entry no_], 
    [db].[company name prod$cust_ ledger entry].[reversed entry no_], 
    [db].[company name prod$cust_ ledger entry].[dimension set id]
    FROM   [db].[company name prod$cust_ ledger entry] 
    SELECT 
    [db].[company name prod$g_l account].[no_], 
    [db].[company name prod$g_l account].[name], 
    [db].[company name prod$g_l account].[account type], 
    [db].[company name prod$g_l account].[global dimension 1 code], 
    [db].[company name prod$g_l account].[global dimension 2 code], 
    [db].[company name prod$g_l account].[gen_ posting type], 
    [db].[company name prod$g_l account].[gen_ bus_ posting group], 
    [db].[company name prod$g_l account].[gen_ prod_ posting group], 
    [db].[company name prod$g_l account].[cost type no_], 
    [db].[company name prod$g_l account].[sru-code] 
    FROM   [db].[company name prod$g_l account]

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    Use Power Query to connect to sql, choose tables and transform data. Then load the data directly to power pivot model.

    Edit:
    Your sql statement is only limited to choose columns, so you can use preview and filter function to select columns and load all tables in one step

    Last edited by ralliartur; May 12th, 2016 at 04:22 AM.

  3. #3
    New Member
    Join Date
    Mar 2009
    Location
    Gothenburg
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    Thank you for your reply.

    The same Table Import Wizard I have in Power Pivot - where via Preview&Filter I can select multiple tables and multiple columns in each. But it is quite time consuming and that's why I asked a question if there is a way to write a long SQL statement instead, where I select xx tables and xx columns in each , then run the code and all tables gets imported into separate tabs/worksheets in Power Pivot. It would be useful for me to have such code and use it whenever I create a new Excel file with Power Pivot.

    Cheers
    Sly


    Quote Originally Posted by ralliartur View Post
    Use Power Query to connect to sql, choose tables and transform data. Then load the data directly to power pivot model.

    Edit:
    Your sql statement is only limited to choose columns, so you can use preview and filter function to select columns and load all tables in one step


  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    That's why i suggested first using power query to connect to db, choose tables, choose columns and load data into power pivot model. I've tried some queries in connection manager but none of them worked for more than one table (always returning first)

  5. #5
    New Member
    Join Date
    Mar 2009
    Location
    Gothenburg
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    Quote Originally Posted by ralliartur View Post
    That's why i suggested first using power query to connect to db, choose tables, choose columns and load data into power pivot model. I've tried some queries in connection manager but none of them worked for more than one table (always returning first)
    So perhaps the easiest way is to run all SQL's separately until all tables gets imported. It would be faster than manual selection of tables/columns anyway....

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    It depends - building separate connections to import tables in power pivot is time consuming too. In power query you are choosing connection once and all tables in the same step. Them obviously you are choosing columns with remove other columns option in pq. Seems easier than using select to new query window in SQL Management Studio for all tables and manually remove unnecessary columns only to copy statement to power pivot for me for each table separately. The only concern is how many tables are you processing for 2 or 3 - i agree separate power pivot seems a good solution, but for >5 i'd prefer power query.

  7. #7
    New Member
    Join Date
    Mar 2009
    Location
    Gothenburg
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    Quote Originally Posted by ralliartur View Post
    It depends - building separate connections to import tables in power pivot is time consuming too. In power query you are choosing connection once and all tables in the same step. Them obviously you are choosing columns with remove other columns option in pq. Seems easier than using select to new query window in SQL Management Studio for all tables and manually remove unnecessary columns only to copy statement to power pivot for me for each table separately. The only concern is how many tables are you processing for 2 or 3 - i agree separate power pivot seems a good solution, but for >5 i'd prefer power query.
    There is no need to create separate connection in Power Pivot to run SQL's one after another.

    I do not use SQL Management Studio at all, just Excel 2013. I have one connection to NAV database, I simply go to Power Pivot, click Manage (then the Power Pivot window opens). I click on Existing Connections, then Open and I can choose either to:

    1) Select from a list of tables and views to choose the data to import - and manually select with Preview&Filter
    2) Write a query that will specify the data to import - or just paste my eariler prepared SQL statement for the specific table.

    I import > 10 tables.

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import multiple tables at once via SQL statement ... ?

    Ok, last chance for me to convince you to try pq for data source with multiple tables:

    You are using excel 2013, so power query is not installed by default, but you can do it on you own. Give power query a chance:

    Selection of tables:



    Choosing columns:

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •