CODEWITHSUNDEEP
sqloracle-database
Trex87
Trex87

Reputation: 15

how to get table name from column value in oracle sql?

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%')
);

And then I will try to merge the data from other tables inside the table that needs to be populated: 

 MERGE INTO  tbl  d 
            USING 
            (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);

Did I provide enough info?

Upvotes: 0

Views: 2596

Answers (1)

Alex_gold__in
Alex_gold__in

Reputation: 26

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;
/

Upvotes: 1

Related Questions