12 Replies Latest reply: Feb 3, 2013 4:34 PM by SomeoneElse RSS

    Insert statements script

    RajeshKanna
      Hi,

      I donot know the what column names exist in the dept table. but I want prepare the insert
      statements as display below

      insert into dept (deptno,dname,loc) values ('10','ACCOUNTING','NEW YORK');
      insert into dept (deptno,dname,loc) values ('20','RESEARCH','DALLAS');
      insert into dept (deptno,dname,loc) values ('30','SALES','CHICAGO');
      insert into dept (deptno,dname,loc) values ('40','OPERATIONS','BOSTON');

      Can any one help me the prepareing select statement or any another method.

      Regards,
      Rajesh
        • 1. Re: Insert statements script
          852736
          It's not clear you want the insert or select statement from your question. Do you want to create the insert statement dynamically ? When you do not know the column names you can get the column names from all_tab_columns view for a given table.
          • 2. Re: Insert statements script
            RajeshKanna
            sorry , I want display the insert statements scripts
            • 3. Re: Insert statements script
              SomeoneElse
              sorry , I want display the insert statements scripts
              Still not getting what you want.
              • 4. Re: Insert statements script
                sb92075
                RajeshKanna wrote:
                sorry , I want display the insert statements scripts
                Oracle, unlike MYSQL, does not have the built in capability to produce INSERT statements for existing table
                • 5. Re: Insert statements script
                  sudher
                  Hi,

                  The below example could bring you some idea...

                  Note:

                  Placeholders are used.. which should be binded with real values when used in PLSQL or other high-level languages.
                  create table object_data as select * from all_objects where 1 = 2;
                  
                  With Tab As (Select 'OBJECT_DATA' Tablename From Dual)
                  Select 'insert into ' || tablename||'(' ||Col_List || ')values (' || Ph_List || ');' 
                  From tab,
                  (
                  Select Listagg(Column_Name,',') Within Group (Order By Column_Id) Col_List,
                         Listagg(Ph,',') Within Group (Order By Column_Id) Ph_List
                  From 
                  (
                  Select Column_Name, Column_Id , ':' ||To_Char(Column_Id) Ph From User_Tab_Cols, tab
                  Where Table_Name = tablename
                  )
                  ) ;
                  
                  Output 
                  ---------
                  
                  insert into OBJECT_DATA(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15);
                  Hope.. the above helps...

                  Regards,
                  Sudher...
                  • 6. Re: Insert statements script
                    sb92075
                    just curious, what exactly populates the bind variables in produced INSERT statement?
                    • 7. Re: Insert statements script
                      stefan nebesnak
                      sudher wrote:
                      Output
                      ---------

                      insert into OBJECT_DATA(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15);
                      execute immediate
                           'insert into OBJECT_DATA( ... ) values (:1,:2,:3, ... )' 
                      using v1, v2, v3, ...;
                      Edited by: stefan nebesnak on Feb 3, 2013 12:07 PM
                      • 8. Re: Insert statements script
                        sb92075
                        stefan nebesnak wrote:
                        sudher wrote:
                        Output
                        ---------

                        insert into OBJECT_DATA(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15);
                        execute immediate
                        'insert into OBJECT_DATA( ... ) values (:1,:2,:3, ... )' 
                        using v1, v2, v3, ...;
                        Edited by: stefan nebesnak on Feb 3, 2013 12:07 PM
                        what populates variable v1, v2, v3, etc.?
                        • 9. Re: Insert statements script
                          stefan nebesnak
                          sb92075 wrote:

                          what populates variable v1, v2, v3, etc.?
                          RajeshKanna wrote:I donot know the what column names exist in the dept table. but I want prepare the insert
                          statements as display below
                          This can be useful when there is known number of columns but their names are unknown.
                          (bind arguments cannot be assigned dynamically)
                          create or replace procedure ins(v1 varchar2, v2 varchar2, v3 varchar2, v4 varchar2, v5 varchar2, v6 varchar2)
                          as
                          
                            /*...*/
                          
                            begin
                          
                             /*...*/   
                            
                              execute immediate
                               'insert into TABLE (' || 
                               col1_from_all_tab_columns || ',' || 
                               col2_from_all_tab_columns || ',' || 
                               col3_from_all_tab_columns || ',' || 
                               col4_from_all_tab_columns || ',' || 
                               col5_from_all_tab_columns || ',' || 
                               col6_from_all_tab_columns || ') values (:1,:2,:3,:4,:5,:6)'
                              using v1, v2, v3, v4, v5, v6; --bind arguments
                            commit;
                          end;
                          • 10. Re: Insert statements script
                            sb92075
                            stefan nebesnak wrote:
                            sb92075 wrote:

                            what populates variable v1, v2, v3, etc.?
                            RajeshKanna wrote:I donot know the what column names exist in the dept table. but I want prepare the insert
                            statements as display below
                            This can be useful when there is known number of columns but their names are unknown.
                            (bind arguments cannot be assigned dynamically)
                            create or replace procedure ins(v1 varchar2, v2 varchar2, v3 varchar2, v4 varchar2, v5 varchar2, v6 varchar2)
                            as
                            
                            /*...*/
                            
                            begin
                            
                            /*...*/   
                            
                            execute immediate
                            'insert into TABLE (' || 
                            col1_from_all_tab_columns || ',' || 
                            col2_from_all_tab_columns || ',' || 
                            col3_from_all_tab_columns || ',' || 
                            col4_from_all_tab_columns || ',' || 
                            col5_from_all_tab_columns || ',' || 
                            col6_from_all_tab_columns || ') values (:1,:2,:3,:4,:5,:6)'
                            using v1, v2, v3, v4, v5, v6; --bind arguments
                            commit;
                            end;
                            why is above superior to SQL below?

                            INSERT INTO DEST_TABLE SELECT * FROM SOURCE_TBL;
                            • 11. Re: Insert statements script
                              stefan nebesnak
                              sb92075 wrote:

                              why is above superior to SQL below?

                              INSERT INTO DEST_TABLE SELECT * FROM SOURCE_TBL;
                              Or..

                              INSERT INTO DEST_TABLE SELECT * FROM table(collection);
                              sb92075 wrote:
                              stefan nebesnak wrote:
                              execute immediate
                              'insert into TABLE (' ||
                              col1_from_all_tab_columns || ',' ||
                              col2_from_all_tab_columns || ',' ||
                              col3_from_all_tab_columns || ',' ||
                              (In above statement you can dynamically switch column names based on input values or instr(colX_from_all_tab_columns , 'something') - it depends on needs)
                              RajeshKanna wrote:
                              sorry , I want display the insert statements scripts
                              But this is out of topic. ツ

                              Edited by: stefan nebesnak on Feb 3, 2013 3:50 PM
                              • 12. Re: Insert statements script
                                SomeoneElse
                                If you're looking for a way to save the contents of a table as INSERT statements, you might consider using a 3rd party tool like Toad or SQL Developer.

                                It's really easy with these tools, usually just right-click/save as/insert statements.

                                Now the real question is...why do you want to do this?