topic Re: Dynamic SQL in Data & Schema Discussions

Dynamic SQL

sathyan75 — Wed, 01 Jun 2022 15:34:27 GMT

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?

Re: Dynamic SQL

RADSr — Wed, 01 Jun 2022 18:34:26 GMT

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.

Re: Dynamic SQL

sathyan75 — Wed, 01 Jun 2022 19:46:31 GMT

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?

Re: Dynamic SQL

RADSr — Wed, 01 Jun 2022 20:45:24 GMT

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-notebook/ta-p/844

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

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 ).

Re: Dynamic SQL

KailaT — Tue, 07 Jun 2022 11:30:56 GMT

Hey @sathyan75 , did this solve your question?