I have a main table that has two columns with table names and id's. And I have those tables with table names in my DB.
For example, I find particular table name, selecting id. And then I want to populate table with that name with data. And I want to do that in one query. How I can do that?
The goal: to populate with data all tables at once, that has the names that similar with values in table name column from main table.
That is how I'm getting the list of tables. I should probably loop through it.
select tbl from asp_tbl where asp in (
select id from (
SELECT * FROM DIMENSION WHERE EXTERNALKEY LIKE 'W16%')
MERGE INTO tbl d
(SELECT ? nums, ? names from data_table) s
ON(d.product = s.product and d.ga = s.ga and d.metric_id = s.metric_id)
WHEN MATCHED THEN UPDATE SET d.names = s.names
WHEN NOT MATCHED THEN INSERT (nums, names)values(s.nums,s.names);
As I understand you need some stored procedure witch may fulfil a table with some test data. If so you may write something like:
create procedure fulfil_test_data (p_table_name varchar2) is begin for x IN (select tbl from asp_tbl where asp in ( SELECT table_id FROM DIMENSION WHERE EXTERNALKEY LIKE p_table_name )) loop execute immediate 'insert into '|| x.tbl ||' (nums, names) select level , chr(ascci(''A'') + mod(level,26)) from dual connect by level < 1001'; end loop; end; /
And call it
begin fulfil_test_data('W16%'); end; /