This discussion is archived
12 Replies Latest reply: Feb 3, 2013 5:05 PM by stefan nebesnak RSS

Insert statements script

RajeshKanna Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    sorry , I want display the insert statements scripts
  • 3. Re: Insert statements script
    SomeoneElse Guru
    Currently Being Moderated
    sorry , I want display the insert statements scripts
    Still not getting what you want.
  • 4. Re: Insert statements script
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    just curious, what exactly populates the bind variables in produced INSERT statement?
  • 7. Re: Insert statements script
    stefan nebesnak Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points