4 Replies Latest reply: May 23, 2016 4:45 AM by kushal chawda RSS

    Dynamic function to load all table

    Hasvine Dhurmea

      Hello All,

       

      I have multiple table in a database:

       

      TEST_2015_01

      TEST_2015_02

      TEST_2015_03

      TEST_2015_04

      TEST_2015_05

      TEST_2015_06

      TEST_2015_07

       

      I need to load all of them in a single table.

       

      I am trying the below but it does not seem to work:

       

      SQL SELECT *

      FROM "ABCD_BKP".dbo.TEST_*;

      Next

       

      Any ideas?

       

      Kind Regards,

       

      Hasvine

        • Re: Dynamic function to load all table
          Stefan Wühl

          You can use the SQLTABLES SQL function to retrieve a list of your tables in your DB. Then iterate over the tables names using FOR ... NEXT

           

          Have a look at your DBMS manual, there are also examples here in the forum how to do this in QV script.

          • Re: Dynamic function to load all table
            kushal chawda

            Tables:

            LOAD * inline [

            Name

            TEST_2015_01

            TEST_2015_02

            TEST_2015_03

            TEST_2015_04

            TEST_2015_05

            TEST_2015_06

            TEST_2015_07 ];

             

            Final:

            LOAD * inline [

            Junk ];

             

            for i=1 to fieldvaluecount('Name')

             

            let vName = fieldvalue('Name',$(i));

             

            concatenate(Final)

            LOAD * ;

            SQL SELECT *

            FROM "ABCD_BKP".dbo.$(vName)";

             

            NEXT i

             

            drop field Junk;

             

            Store Final into ..\Final.QVD;

              • Re: Dynamic function to load all table
                Hasvine Dhurmea

                Hi Kushal,

                 

                I used to do that but the problem is that every month a new table is created in the database.

                 

                We have already reached like table TEST_2016_04

                 

                I need a dynamic way to do it.

                 

                Many Thanks,

                 

                Hasvine

                  • Re: Dynamic function to load all table
                    kushal chawda

                    I have generated Table Name from Jan 2015 to Current month

                     

                    Table:

                    LOAD 'TEST_' & date(Monthstart(Date),'YYYY_MM') as Name;

                    LOAD makedate(2015,1,1)+iterno()-1 as Date

                    Autogenerate 1

                    While makedate(2015,1,1)+iterno()-1 <= today();

                     

                    Final:

                    LOAD * inline [

                    Junk ];

                     

                    for i=1 to fieldvaluecount('Name')

                     

                    let vName = fieldvalue('Name',$(i));

                     

                    concatenate(Final)

                    LOAD *,

                            '$(vName)' as TableName;

                    SQL SELECT *

                    FROM "ABCD_BKP".dbo.$(vName)";

                     

                    NEXT i

                     

                    drop field Junk;

                     

                    Store Final into ..\Final.QVD;