Oracle - How can I create one insert with multiple values dynamically

Question

I want to create one INSERT statement with multiple values dynamically like to following example:

INSERT INTO table_name (ID, PARENT, NAME, ENABLED) 
VALUES (1, 't1', 'Test1', 1 ), (2, 't2', 'Test2', 1 ), (3, 't3', 'Test3', 1 );

Currently I'm working with the following statement, but now I get too many individual statements and this doesn't deliver good performance

SELECT 'INSERT INTO table_name(ID, PARENT, NAME, ENABLED) ' 
|| 'VALUES ('|| ID ||','''|| PARENT ||''','''|| NAME || ''', '|| ENABLED ||');'
FROM table_name WHERE ID IN (... [inner select] ...);

How is it possible to create a loop which attached new values ins a single insert statement like this?

SELECT 'INSERT INTO table_name (ID, PARENT, NAME, ENABLED)' VALUES

DECLARE
    myValues varchar2(500);
    CURSOR myCur IS SELECT ID, PARENT, NAME, ENABLED FROM table_name;
BEGIN
    FOR values IN myCur LOOP

        IF myCur%ROWCOUNT = 1 THEN
            myValues := '('||values.ID ||', '''|| values.PARENT ||''', '''|| values.NAME ||''', '|| values.ENABLED ||'),';
        ELSE
            myValues := '('||values.ID ||', '''|| values.PARENT ||''', '''|| values.NAME ||''', '|| values.ENABLED ||')';
        end if;
    END LOOP;
END;
--...

I need this to export my Data and insert into an other Database by using sqlplus

thanks all

MT0 · Accepted Answer · 2019-05-20 11:00:53Z

Use INSERT INTO ... SELECT with a hierarchical query if you want to insert static incrementing data (as per your original example):

INSERT INTO table_name ( id, parent, name, enabled )
  SELECT LEVEL, 't' || LEVEL, 'Test' || LEVEL, 1
  FROM   DUAL
  CONNECT BY LEVEL <= 3

If you want to build a query from an existing table then:

SELECT 'INSERT INTO table_name ( id, parent, name, enabled )'
       || LISTAGG( 
            'SELECT ' || id || ', '
                    || '''' || REPLACE( parent, '''', '''''' ) || ''', '
                    || '''' || REPLACE( name, '''', '''''' ) || ''', '
                    || enabled
            || ' FROM DUAL',
            ' UNION ALL '
          ) WITHIN GROUP ( ORDER BY ROWNUM )
FROM   table_name

If it is going to be longer than 4000 characters then you can generate multiple rows:

SELECT 'INSERT INTO table_name ( id, parent, name, enabled )' FROM DUAL
UNION ALL
SELECT 'SELECT ' || id || ', '
                 || '''' || REPLACE( parent, '''', '''''' ) || ''', '
                 || '''' || REPLACE( name, '''', '''''' ) || ''', '
                 || enabled
       || ' FROM DUAL'
       || CASE WHEN ROWNUM < COUNT(*) OVER () THEN ' UNION ALL ' END
FROM   table_name

db<>fiddle here

Or use the Oracle utilities RMAN or ExpDP to generate a backup of the table.

Littlefoot · Accepted Answer · 2019-05-20 10:45:18Z

0

In my opinion, you should stop reinventing the wheel. Oracle offers data pump export/import utilities which are designed for such a purpose - moving data around. In simple (or, should I say most) cases, you can even use the original export/import utilities.

You didn't specify database version you use so - here's 12c Data Pump documentation: https://docs.oracle.com/database/121/SUTIL/GUID-501A9908-BCC5-434C-8853-9A6096766B5A.htm#SUTIL2877

There are quite a few examples which will show how to do that. I'd suggest you not to skip reading documentation and jumping straight to running some code. You'd benefit in knowing what you're actually doing, instead of hoping that it'll turn right.

answered May 20, 2019 at 10:45

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Radagast81 · Accepted Answer · 2019-05-20 10:46:32Z

One way is the usage of XML-Files for the data transfer.

Get the data as XML:

DECLARE
  l_xml        DBMS_XMLGEN.ctxHandle;
BEGIN
  l_xml := dbms_xmlgen.newContext('SELECT * FROM table_name WHERE ID IN (... [inner select] ...)');
  dbms_output.put_line(dbms_xmlgen.getXML(l_xml));
  dbms_xmlgen.closeContext(l_xml);
END;

Insert Statement:

INSERT INTO table_name (ID, PARENT, NAME, ENABLED)
SELECT dbms_xmlgen.convert(value(soc).EXTRACT('/ROW/ID/text()'     ).getStringVal(), 1)
     , dbms_xmlgen.convert(value(soc).EXTRACT('/ROW/PARENT/text()' ).getStringVal(), 1)
     , dbms_xmlgen.convert(value(soc).EXTRACT('/ROW/NAME/text()'   ).getStringVal(), 1)
     , dbms_xmlgen.convert(value(soc).EXTRACT('/ROW/ENABLED/text()').getStringVal(), 1)
  FROM TABLE(XMLSequence(EXTRACT(XMLType(<XML>), '/ROWSET/ROW'))) SOC

Collectives™ on Stack Overflow

Oracle - How can I create one insert with multiple values dynamically

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related