Search code examples
sqloracle11goracle12c

How to use dynamic sql to insert into select from "dynamic table"?

I want to dynamically pass the table name to my insert into select statement. However, I am getting missing keyword error when executing below block.

declare
v_tbl varchar2(4000):='TBL';
begin
execute immediate 'Insert into emp(col1, col2, col3)
                   select fname, lname, origin 
                   from source src
                   inner join v_tbl on src.id=tab.id';
end;

I am doing this dynamically because there will be more than one table I will loop through the dynamic sql. There are multiple rows from the select statement so I can't pass the values to a variable.

Solution 
  • declare
v_tbl varchar2(4000):='TBL';
begin
execute immediate 'Insert into emp(col1, col2, col3)
                   select fname, lname, origin 
                   from source src
                   inner join ' || v_tbl ||' on src.id=tab.id';
end;