CODEWITHSUNDEEP
oracle-databasesqlplus
schreibfehlr
schreibfehlr

Reputation: 3

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

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

Upvotes: 0

Views: 159

Answers (3)

MT0
MT0

Reputation: 168291

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.

Upvotes: 2

Radagast81
Radagast81

Reputation: 3016

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142993

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.

Upvotes: 0

Related Questions