6 Replies Latest reply: Jul 16, 2009 11:45 AM by SanjayRs RSS

    How to export data and SQL sentence?

    712146
      Hi all,

      I need to create SQL sentences with INSERT INTO clauses of exported data from a table to populate another table?

      Thanks and best regards,

      Carlos N.
        • 1. Re: How to export data and SQL sentence?
          SanjayRs
          Simply
          Insert into table1 select * from table2 ;
          SS
          • 2. Re: How to export data and SQL sentence?
            712146
            Thanks Sänjay...

            What I need to do is:

            I have my table with all data in development site, I have to give my customer the first time population of this table in production, and they want a INSERT INTO script to do that.

            So, is my source table has N rows, I should create somethins like:

            INSERT INTO my_table (my_field_1, my_field_2) VALUES ('value_1', 'value_2');
            INSERT INTO my_table (my_field_1, my_field_2) VALUES ('value_3', 'value_4');
            (...)
            INSERT INTO my_table (my_field_1, my_field_2) VALUES ('value_N', 'value_N');

            I don't know how to export and then create this script automatically, is there any way/tool to do that? Is a little bit complicated to do it manually due we have many tables with many rows.

            Any ideas which points me in the right way?

            Thanks and regards,
            Carlos N.
            • 3. Re: How to export data and SQL sentence?
              712293
              try something like

              select 'insert into my_table (fld1, fld2) values ( ''' || lookup_code || ''',''' || meaning || ''');' from fnd_lookup_values
              where lookup_type = 'PROBABILITY_DETAILS'

              OR if you have TOAD, you can query your table in Development and 'save as' insert statement.
              • 4. Re: How to export data and SQL sentence?
                LKBrwn_DBA
                Use [SQL Developer|http://www.oracle.com/technology/products/database/sql_developer/index.html] (free from Oracle):

                Open Connection > Select Schema > Select Tables
                Right Click on table > Export Data > Insert

                Done! ;)
                • 5. Re: How to export data and SQL sentence?
                  SanjayRs
                  Create insert script
                  Here is an example
                  SQL> select ' insert into emp (empno,ename) values ('||empno||','''||ename||''');' from emp ;
                  
                  'INSERTINTOEMP(EMPNO,ENAME)VALUES('||EMPNO||','''||ENAME||''');'
                  ----------------------------------------------------------------------------------------------
                   insert into emp (empno,ename) values (7369,'SMITH');
                   insert into emp (empno,ename) values (7499,'ALLEN');
                   insert into emp (empno,ename) values (7521,'WARD');
                   insert into emp (empno,ename) values (7566,'JONES');
                   insert into emp (empno,ename) values (7654,'MARTIN');
                   insert into emp (empno,ename) values (7698,'BLAKE');
                   insert into emp (empno,ename) values (7782,'CLARK');
                   insert into emp (empno,ename) values (7788,'SCOTT');
                   insert into emp (empno,ename) values (7839,'KING');
                   insert into emp (empno,ename) values (7844,'TURNER');
                   insert into emp (empno,ename) values (7876,'ADAMS');
                   insert into emp (empno,ename) values (7900,'JAMES');
                   insert into emp (empno,ename) values (7902,'FORD');
                   insert into emp (empno,ename) values (7934,'MILLER');
                  
                  14 rows selected.
                  You can add necessary commands to set feedback off , set pagesize 0 and spool it to a files using sqlplus.

                  SS
                  • 6. Re: How to export data and SQL sentence?
                    SanjayRs
                    Inserting one row at a time is inefficient.
                    You can do this instead
                    SQL> select decode(rownum,1,'Insert all'||chr(13),'     ')||
                      2  'into emp (empno,ename) values ('||empno||','''||ename||''')' from emp
                      3  /
                    
                    DECODE(ROWNUM,1,'INSERTALL'||CHR(13),'')||'INTOEMP(EMPNO,ENAME)VALUES('||EMPNO||','''||ENAME||''
                    ------------------------------------------------------------------------------------------------
                    Insert all into emp (empno,ename) values (7369,'SMITH')
                         into emp (empno,ename) values (7499,'ALLEN')
                         into emp (empno,ename) values (7521,'WARD')
                         into emp (empno,ename) values (7566,'JONES')
                         into emp (empno,ename) values (7654,'MARTIN')
                         into emp (empno,ename) values (7698,'BLAKE')
                         into emp (empno,ename) values (7782,'CLARK')
                         into emp (empno,ename) values (7788,'SCOTT')
                         into emp (empno,ename) values (7839,'KING')
                         into emp (empno,ename) values (7844,'TURNER')
                         into emp (empno,ename) values (7876,'ADAMS')
                         into emp (empno,ename) values (7900,'JAMES')
                         into emp (empno,ename) values (7902,'FORD')
                         into emp (empno,ename) values (7934,'MILLER')
                    select * from dual ; --- // Add this statement manually or in the script 
                    -- // using prompt statement in sqlplus 
                    SS