Generating SQL Insert into for Oracle

How to generate INSERT Statements with Subqueries in Oracle SQL Developer?

Simple write a query that produces the required data (with the mapped key) using a join of both tables.

For example (see the sample data below) such query (mapping the unique_value to the id):

select 
  tab.col1, tab.col2, lookup_table.id fkcol 
from tab
join lookup_table
on tab.fkcol = lookup_table.unique_value

      COL1 COL2        FKCOL
---------- ------ ----------
         1 value1         11
         2 value2         12

Now you can use the normal SQL Developer export feature in the INSERT format, which would yield following script - if you want to transer it to other DB or insert it direct with INSERT ... SELECT.

Insert into TABLE_NAME (COL1,COL2,FKCOL) values ('1','value1','11');
Insert into TABLE_NAME (COL1,COL2,FKCOL) values ('2','value2','12');

Sample Data

select * from tab;
      COL1 COL2   FKCOL  
---------- ------ -------
         1 value1 unique 
         2 value2 unique2
         
select * from  lookup_table       
       ID UNIQUE_
---------- -------
        11 unique 
        12 unique2

Generate Insert statements for result set in Oracle

Run your query in SQL Developer.

In the result grid, right click. Select Export...

A popup dialog is presented asking for how to export the result. The default is insert. You can select a file to save to, or copy to clipboard, or various other options.

This is on SQL Developer 3.0.4.

Generatting insert statement for given table/column value dynamically

Try this procedure. Note that it is good for NUMBER, VARCHAR, DATE (when has a default format) data types

set serveroutput on
create or replace
procedure p_export_data_dml(p_table in varchar2, p_filter_column in varchar2, p_filter_value in varchar2, p_dmls in out varchar2)
is
  cursor c_statements(p_table varchar2, p_filter_column varchar2, p_filter_value varchar2) is
    select 'select ''insert into '||p_table||' ('||
        listagg(column_name, ', ') within group (order by column_id) ||') values(''''''||'||
        listagg(column_name, '||'''''', ''''''||') within group (order by column_id)||'||'''''');'' from '||p_table||' where '||p_filter_column||' = '''||p_filter_value||'''' insert_statement
    from user_tab_columns
    where table_name = upper(p_table);

  v_output varchar2(4000);
  v_sql varchar2(4000);
  type t_cursor is ref cursor;
  c_cur t_cursor;
begin
  for r_statements in c_statements(p_table, p_filter_column, p_filter_value) loop
      v_sql := r_statements.insert_statement;
      dbms_output.put_line(v_sql);
      open c_cur for v_sql;
      loop
        fetch c_cur into v_output;
        exit when c_cur%notfound;
        if p_dmls = null then
          p_dmls := v_output;
        else
          p_dmls := p_dmls || '
        '||v_output;
        end if;
      end loop;
      close c_cur;
  end loop;
end;
/

Then you can try executing using the below

declare
  v_text varchar2(32000);
begin
  p_export_data_dml('t1', 'c1', 10, v_text);
  dbms_output.put_line(v_text);
end;
/

The output can be something like this:

    insert into t1 (C1, C2, C3, C4) values('10', '1', '11', '12-SEP-2017 07:54:38');
    insert into t1 (C1, C2, C3, C4) values('10', '2', '12', '12-SEP-2017 07:54:38');
    insert into t1 (C1, C2, C3, C4) values('10', '3', '13', '12-SEP-2017 07:54:38');

Is there an Oracle SQL tool that builds insert statements from a result set?

I found this solution, which is what I'm using now. Thanks for all of the help.
It turns out we can use SQL+ too. For some reason I can't run it in SQL Station.

COPY FROM userid/password@from_DB TO userid/password>@to_DB INSERT toDB_tablename USING SELECT * FROM fromDB_tablename where ....;

commit;

How to generate single insert statement for full table data using SQL Developer?

The simplest method would be to create an anonymous PL/SQL block:

begin
    --add 1 million inserts here
    commit;
end;
/

This eliminates almost all of the network round-trips. It's not the most efficient solution but it's a simple change and is very close to the most efficient solution.

Creating an anonymous block that large may result in an error like PLS-00123: program too large (Diana nodes). If that happens break the program up into
smaller chunks and try again.

dynamically generate sql insert statements from results of a query

It's pretty easy. Just take your existing query, and modify your SELECT to concatenate the INSERT statement with the g.id value.

FYI: || is Oracle's string concatenation operator. I'm not familiar with DB2, but I believe it supports the same syntax for concatenating strings. So the query should work for both databases (I hope).

select 'insert into SUV_OWNER.GRUPPIRUOLIPROCEDURE (GRUPPOID, RUOLOID, PROCEDURAID) values (' || g.id || ', 141, 7);'
from SUV_OWNER.gruppi g 
WHERE EXISTS (
  SELECT 1 
  FROM SUV_OWNER.GRUPPIRUOLIPROCEDURE grp 
  WHERE grp.gruppoid=g.gruppoid 
  AND GRP.RUOLOID = 50)
AND G.CHIAVE LIKE 'ANA%';