I am working on certain SQL code in Teradata SQL assistant, but I am encountering some issues. Could you please help me to resolve it ? Your help will be really appreciated.
So, we have one table with one column, and values in the column are again some tables in Teradata. Now, I would like to create a process to select data from the each row (column value) and insert into another table. But, I would like to do it without manaul efforts of running insert statements and then select into source table.
For Example - Table name is 'Demo_Table' and Column name is 'Demo_Column' and values in that column are 'Table_A','Table_B'. Now, we would like to automate this in a such a way that, all of the data in the 'Table_A' and 'Table_B' can be inserted into another final table ('Final_Table') with automated process, like:
INSERT INTO Final_Table SELECT * FROM Table_A
INSERT INTO Final_Table SELECT * FROM Table_B
But, we are looking to automate this process than executing insert statements manually.
Your help will be really appreciated. Thanks in advance !!
It seems a case for dynamic SQL.
You can do it using a cursor for the DEMO_TABLE.DEMO_COLUMN And iterate using dynamic SQL (SYSEXECSQL())
CALL DBC.SYSEXECSQL('INSERT INTO Final_Table SELECT * FROM ' || :TheTable || ';');