Dynamic SQL

[sathyan75]

Is there an option to build a query using variables. I have the table names in a lookup table and i would like to use that to build dynamic SQL in Incorta. Is there such an option available in Incorta?

[RADSr]

What's your goal?  Is the SQL you want to build to ingest data into an Incorta table?  Or to create an Incorta derived table?  Or for a specific report output?  

Incorta has a lot of flexibility built in and more available w/ notebooks so the answer is probably "yes" but to give a definite answer a specific use case would be helpful.

[sathyan75]

We have 30 different tables and each of these table names are in a look up table column. I would like to run through a loop wherein each of these table names are fetched from this lookup table and we create a dynamic select query on this table name and pull the data from this table and load all these into one table. How can this be achieved in Incorta without creating 30 different tables and 30 multiple data sources? 

[RADSr]

To be clear - you have 30 tables sitting in a database and a thirty-first table containing the names of the first thirty.  You want to create a single Incorta table containing the data held within all thirty source tables and, I presume, an additional column with the source table name included?

Why seems to be the next logical question  😉     

But if you really want to do that there's going to be some manual work - just for starters you need to know how many columns to define in Incorta ( max columns + 1  I'd guess ) and then string together select <sourcetablename>, * from sourcetablename statements.      

You are probably looking at a notebook using R or something similar - for that I'd start here and here:

https://community.incorta.com/t5/data-schemas-knowledgebase/getting-started-with-r-in-incorta-notebo...

https://stackoverflow.com/questions/36192437/merging-r-dataframes-with-different-number-of-columns-a...

Of course this gets a lot easier if you have 30 source tables which are structurally identical and you can just use  UNION ALL between all thirty result sets  ( or just UNION if you include the original table name ).

[KailaT]

Hey @sathyan75 , did this solve your question?