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

Question

I need to move some data from Environment A to Environment B. So far, so easy. But some of the columns have FK Constraints and unfortunately the lookup data is already on Environment B and has different PKs. Lucky me, there are other unique columns I could do a mapping on. Therefore I wonder if SQL Developer has an export feature which allows me to replace certain column values by subqueries. To make it clear, I'm looking for a SQL Developer Feature, Query or similar which generates INSERT Statements that look like this:

INSERT INTO table_name(col1, col2, fkcol) 
VALUES('value', 'value', (SELECT id FROM lookup_table WHERE unique_value = 'unique'))

My best approach was to try to generate them by hand, like this:

SELECT 
    'INSERT INTO table_name(col1, col2, fkcol) '  
    || 'VALUES( '
    || (SELECT LISTAGG(col1, col2, 
'SELECT id FROM lookup_table 
WHERE unique_value = ''' || lookup_table.uniquevalue || '''', ', ') 
WITHIN GROUP(ORDER BY col1) 
FROM table_name INNER JOIN lookup_table ON table_name.fkcol = lookup_table.id)
    || ' );'
    FROM table_name;

Which is absolutely a pain. Do you know something better to achive this without approaching the other db?

Import the data as another table and do insert ... select with the mapping you need — astentx
– astentx, Commented Jul 20, 2021 at 14:22

Marmite Bomber · Accepted Answer · 2021-07-20 14:33:00Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related