Skip to Main Content
  • Questions
  • Create Insert Statements Dynamically

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Maverick.

Asked: April 07, 2008 - 11:30 am UTC

Last updated: May 04, 2021 - 9:54 am UTC

Version: 10.2.0

Viewed 100K+ times! This question is

You Asked

Tom,How do you create insert statments dynamically if I give a table name? in TOAD tool, they have this option for each table [Create insert statements] and I was wondering what kind of logic they might have used to create them.

I was trying it myself and got stuck at getting data from a given table and puttign them in local variables..

Do you have any solution to this?

Thanks,

and we said...

I would *never* do that - it would be just about the least efficient way to move data.

Basically, you would write a program, it would dynamically prepare and describe a query, and then print it out. Basically - it is just a report - so you would do it in the same way you would code a report - except instead of making things line up and look pretty - you generate insert statements.

I have scripts to unload data - http://asktom.oracle.com/tkyte/flat - but I will not generate insert statements for you.

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Insert statements..

Maverick, April 08, 2008 - 10:33 am UTC

Thanks Tom, But I am not planning to move data using that script.
Like I said, TOAD Creates this script [ofcourse it's not dynamically generated]. I have to select a table to get insert statements. So I was wondering is there a way to create scripts like that If I pass in a TABLE NAME as parameter. This script has to pick up all the column names and create an insert statement..
Just to see whether it can be done using pl/sql or not?

Thanks, for any ideas [Did not mean for you to create whole script for me]. Just some ideas as to how can I fetch data into variables dynamically..
sample pseudo code would work :-)

Thanks,
Tom Kyte
April 09, 2008 - 2:29 pm UTC

...[ofcourse it's not dynamically
generated]..

of course it IS dynamically generated - no clue what you mean there.

You can write *CODE* do this this, obviously - plsql can do it, it is just like printing a report - just as I said.

dbms_sql, search for it on this site - but be aware, if you did not know of the way to dynamically execute sql in plsql, this might not be the best "first program" for you to code with. You might start with smaller fish.

ALL_TAB_COLUMNS

Rivas, April 08, 2008 - 10:52 am UTC

Hi Maverick,

You may query ALL_TAB_COLUMNS dictionary view in order to get the column name of a table given its name.

In pl/sql you may then write a cursor to fetch all the column names and build the insert statement in each iteration...

hope this helps..
Tom Kyte
April 09, 2008 - 2:31 pm UTC

or build the string 'select * from ' || table (being careful to avoid sql injection of course, but that is another discussion)

and then dbms_sql to open and describe the cursor - which will tell you the datatypes and names of the columns

so you can build the 'insert into table ' || table || '( column-names ) values (' string

so you can fetch from the table and then finish the string and dbms output it.

Thanks Rivas..but

Maverick, April 08, 2008 - 1:43 pm UTC

problem comes when you fetch those values into variables. Eg: I am trying to do this for a table that has 5 columns in it. I get all those from all_tab_columns and can buid
Insert into table (col1,col2,col3,col4,col5)
  values(

Now putting those values in the string, is where the complexity is ..
I need to select those columns from table , execute them dynamically and bulk collect into variables. If it was a known table during run time, we could do that. But if I were to build something dynamically [generic] for any table..that's not possible [let's say I'm not able to]
..Any ideas?
Tom Kyte
April 09, 2008 - 2:40 pm UTC

see above, read everything you can about dbms_sql and write code.

A reader, April 09, 2008 - 1:41 am UTC

Sorry, but I'm not understanding your problem,

We agreed that you can build a statement like:
INSERT INTO any_table ( col1, col2, col3, col4, col5) VALUES (:1, :2, :3, :4, :5); 


In which col1..5 are the column names of any_table retrieved dinamically from the ALL_TAB_COLUMNS table.

Now you need five variables for the USING clase. ┬┐Is there any problem picking these values into five variables? If you want to insert values regardless the table, use dynamic sql again.

Are we talking the same scenario? Can you provide a use-case of this script?

Thanks,

Not 5 variables

Maverick, April 09, 2008 - 7:54 am UTC

I think you missed a small point in this scenario. I will not be having only 5 columns in all tables. It could vary. a table can have 2 columns or three columns or n columns.
So, I need to vary the fetch into variables dynamically. Hope I'm not confusing you more.

Steps would be like this:

1)Select columns from all_tab_columns for a given table.
eg: Need to create Insert statements for EMP ->
select column_name bulk collect into empColumnarray
from user_tab_columns
where table_name='EMP';

2)using this column list, select the data from that table:
eg: select [empColumnarray]
Bulk collect into empRecordType
from EMP;

3) now build Inserts:
Insert into EMP(empColumnArray) values (empRecordType.empno,empRecrodType.Ename...);

problem is in last statment I cannot say empno,ename,etc. It has to be dynamic. it changes for DEPT table,CUSTOMER Table etc..

Hope this helps.
Thanks,


Insert using PLSQL

A reader, April 09, 2008 - 8:45 am UTC

Following sample code can be used to generate insert statement. I have used very limited data-types in the solution (number, date and varchar2 only). Also note that dbms_output is restricted to 255 characters. So, if the length of 'insert into ' exceeds 255, the query will fail. There are number of workarounds which can be implemented to avoid this error.


set serveroutput on size 100000
set feedback off

declare
  v_table_name      varchar2(30) := 'EMP';  -- Your Tablename
  v_column_list     varchar2(2000);
  v_insert_list     varchar2(2000);
  v_ref_cur_columns varchar2(4000);
  v_ref_cur_query   varchar2(2000);
  v_ref_cur_output  varchar2(2000);
  v_column_name     varchar2(2000);
  cursor c1 is select column_name, data_type from user_tab_columns where table_name = v_table_name order by column_id;
  refcur            sys_refcursor; 
begin
  for i in c1 loop
     v_column_list := v_column_list||','||i.column_name;
     if i.data_type = 'NUMBER' then
        v_column_name := i.column_name;
     elsif i.data_type = 'DATE' then
        v_column_name := chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||chr(39)||'dd/mm/yyyy hh:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||chr(39)||'dd/mm/rrrr hh:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     elsif i.data_type = 'VARCHAR2' then
        v_column_name := 'chr(39)||'||i.column_name||'||chr(39)';
     end if;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  end loop; 
  v_column_list     := ltrim(v_column_list,',');
  v_ref_cur_columns := substr(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||v_table_name;
  
  open refcur for v_ref_cur_query;
  loop
  fetch refcur into v_ref_cur_output; 
  exit when refcur%notfound;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := replace(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := replace(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := replace(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := replace(v_ref_cur_output,'null,)','null,null)');
    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    dbms_output.put_line (v_ref_cur_output); 
  end loop; 
end;
/


HTH

I didn't think about it..very useful

Maverick, April 09, 2008 - 10:07 am UTC

Thanks "Reader",
you are basically concatenating all the columns and outputting them into a single VARCHAR2 variable, and breaking them by a delimiter. I didn't think of that at all. Thanks a lot.
I could use this and proceed further.


Sachin, April 10, 2008 - 1:24 am UTC

you can create insert statment,through spooling.


set echo off
set feedback off
set term off
set pagesize 0
set linesize 8000
set heading off
SPOOL C:\upgrade\scripts\data\3.0\event_schema\data\db_settings.sql
prompt set define off
prompt set echo off
prompt set feedback off
prompt set term off
prompt set pagesize 0
prompt set linesize 8000
prompt set heading off
prompt delete from db_settings
prompt /
select 'insert into db_settings values('''||setting_name||''','''||data_type||''','''||replace(value,'''','''''')||''','''|| replace(description,'''','''''')||''''||')'||';' from db_settings;
prompt commit;
prompt /
prompt set define on
prompt set echo on
prompt set feedback on
prompt set term on
set heading on
SPOOL OFF
set echo on
set feedback on
set term on
set heading on

Insert statements

Mark, April 13, 2008 - 1:47 am UTC

Hi, we have a requirement that install scripts create a spool file of all the activities. I've recently being working on a script to be called from the main install script to create insert statements from data within a table before it is dropped. This is mainly incase a tester re-runs a script without backing up their data. As I'm already spooling to a log file and am only on Oracle 9i the script spools its generated sql statmenet to the unix box to an area which is accessible via a url. I then run the file by referencing the url + filename. This is a first draft of the script. It does not fully work if the number or xmltype columns are null but an addition of a decode around these should do the trick. I pass in 2 parameters when calling the script, first the table name and second a name for the temp file on the unix box.


SET VERIFY OFF

DECLARE


CURSOR column_names_cur
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;


CURSOR col_select_cur
IS
SELECT DECODE(data_type ,
'DATE' , ''''||'TO_DATE('||''''||'||'||''''||''''||''''||''''||'||'||'TO_CHAR(' ,
'NUMBER', NULL,
'VARCHAR2', ''''''''''||'||' ,
'XMLTYPE', ''''||'xmltype('||''''||'||'||''''||''''||''''||''''||'||')

||column_name
||DECODE (data_type ,
'DATE' , ','||''''||'dd-mon-yyyy hh24:mi:ss'||''''||')'||'||'||''''||''''||''''||''''||'||'||''''||','||''''||'||'||''''||''''||''''||''''||'||'||''''||'dd-mon-yyyy hh24:mi:ss'||''''||'||'||''''||''''||''''||''''||'||'||''''||')'||'''' ,
'NUMBER', NULL ,
'VARCHAR2' , '||'||''''||''''||''''||'''' ,
'XMLTYPE', '||'||''''||''''||''''||''''||'||'||''''||')'||'''' ) sel
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;


w_sql_start VARCHAR2(4000) ;
w_sql VARCHAR2(4000) ;
w_ok BOOLEAN ;
w_file_handle utl_file.file_type ;
w_err_text VARCHAR2(500) ;
w_utl_dir system_default_parameters.sdp_value%TYPE ;
w_col_sep VARCHAR2(400) := '||'',''||' ;


BEGIN

-- obtain the start of the sql statement
FOR column_names_rec IN column_names_cur
LOOP

w_sql_start := w_sql_start ||column_names_rec.column_name || ',' ;

END LOOP ;


w_sql_start := 'SELECT ''INSERT INTO &&1 ('|| RTRIM (w_sql_start,',') ||') VALUES (' ||''''||'||';


-- obtain individual columns

FOR col_select_rec IN col_select_cur
LOOP

w_sql := w_sql || col_select_rec.sel ||w_col_sep ;

END LOOP ;

IF w_sql IS NOT NULL
THEN
w_sql := SUBSTR(w_sql, 1, LENGTH(w_sql)-7) ;
w_sql := w_sql_start || w_sql ||'||'||''''||');'||''''||' AS "insert.sql" FROM &&1 ;' ;
ELSE
w_sql := 'SELECT ''Table &&1 not found.'' AS "insert.sql" FROM DUAL; ' ;
END IF ;


-- write the select statement which will genearte the insert statement to a file on the unix box

w_file_handle := utl_file.fopen ('/app/webrep/webreports', -- directory ,
'&&2' , -- filename ,
'W') ;


utl_file.putf(w_file_handle ,
'%s' ,
w_sql) ;



utl_file.fclose(w_file_handle) ;

END ;
/


COLUMN url_and_file new_value url_and_file

SELECT ' http://my_url_to_file_location' ||'&&2' "url_and_file"
FROM DUAL
/

-- Exec the file from unix by calling it via its web address

@'&url_and_file'



-- Tidy up

SET VERIFY ON

UNDEFINE 1
UNDEFINE 2
UNDEFINE url_and_file


Tom Kyte
April 13, 2008 - 8:47 am UTC

seems that for an install script, it would be so much easier to

just backup the data into temporary tables before running it, eg: instead of creating a fragile text file that may or may not work in the end - or might lose data (character set issues, whatever), you would just

insert into backup_of_important_stuff
select 'install name', sysdate, a.* from table a;


seems a lot easier and safer no?

Update

Naveen, April 15, 2008 - 1:45 pm UTC

I also faced the same situation i.e i has to generate "Insert statements dynamically".So wrote a query for that The query is :

Select 'insert into t values('||col1||','||col2||','||col3||','||col4||')' from xyz.

This would generate the insert statements.

A reader, July 04, 2011 - 6:26 am UTC


Generating dynamic insert statement

Zahirul Haque, June 07, 2012 - 9:33 pm UTC

Code by HTH is useful, but need some improvements, e.g. it does not handle single quote in the text field, and serveroutput for huge table.
Tom Kyte
June 08, 2012 - 7:12 am UTC

go for it - you are a programmer right? You have 90% of what you need - seriously.

Generate Insert statement dynamically

Zahirul Haque, August 28, 2012 - 7:42 pm UTC

You may find situations where you need to create insert statement dynamically. Here is the code you can use. I have modified code by HTH, and it works:

----------------------------------------------
-- Script to generate insert statement dynamically
-- Written by HTH
-- Improved by Zahirul Haque
-- Aug. 29, 2012
----------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET PAGESIZE 0
SET FEEDBACK OFF
SET DOC OFF
SET TAB OFF
SET LINESIZE 4096 
SET TRIM ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT OFF

SPOOL insert_me.sql


DECLARE
TYPE num_t IS TABLE OF number index by binary_integer;
     l_count_t num_t;
TYPE tab_t IS TABLE OF VARCHAR2(35);
tableList tab_t := tab_t(
    'EMP'
   ,'DEPT'
   ,'REGION'
   ,'COUNTRY'
   -- add more tables here
);

table_nam   VARCHAR2(34);
l_count     NUMBER;

PROCEDURE gen_insert (p_tab_name VARCHAR2, p_exist BOOLEAN default false)
IS
  v_column_list     VARCHAR2(4096) := null;
  v_insert_list     VARCHAR2(16096);
  v_ref_cur_columns VARCHAR2(16096) := null;
  v_ref_cur_query   VARCHAR2(16000);
  v_ref_cur_output  VARCHAR2(16000) := null;
  v_column_name     VARCHAR2(256);
  CURSOR c1 IS
       SELECT column_name, data_type FROM user_tab_columns
       WHERE table_name = p_tab_name ORDER BY column_id;
  refcur            sys_refcursor; 
BEGIN
  DBMS_OUTPUT.ENABLE(NULL); -- this will hangle dbms_output for huge table.
  FOR i IN c1 LOOP
     v_column_list := v_column_list||','||i.column_name;
     IF i.data_type = 'NUMBER' THEN
        v_column_name := i.column_name;
     ELSIF i.data_type = 'DATE' THEN
        v_column_name := chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||
        chr(39)||'dd/mm/yyyy hh24:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||
        chr(39)||'dd/mm/rrrr hh24:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     ELSIF i.data_type = 'VARCHAR2' then
        -- Following line will hangle single quote in text data.
        v_column_name := 'chr(39)||replace('|| i.column_name ||','''''''','''''''''''')||chr(39)';
        --dbms_output.put_line(v_column_name);
     ELSIF i.data_type = 'CHAR' then
        v_column_name := 'chr(39)||'|| i.column_name ||'||chr(39)';
     END IF;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  END LOOP;
     --dbms_output.put_line('A='||v_ref_cur_columns);

IF v_column_list is null then
  dbms_output.put_line('--Table '|| p_tab_name || ' does not exist');
ELSE
  --dbms_output.put_line(p_tab_name || ' does exist');
  v_column_list     := LTRIM(v_column_list,',');
  v_ref_cur_columns := SUBSTR(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||p_tab_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||p_tab_name;
  --dbms_output.put_line(v_ref_cur_columns);
  --dbms_output.put_line(v_ref_cur_query);
 
  OPEN refcur FOR v_ref_cur_query;
  LOOP
  FETCH refcur INTO v_ref_cur_output; 
  EXIT WHEN refcur%NOTFOUND;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := REPLACE(v_ref_cur_output,'null,)','null,null)');

    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    DBMS_OUTPUT.PUT_LINE (v_ref_cur_output); 
  END LOOP; 
IF v_ref_cur_output is null then
  dbms_output.put_line('--No data in '||p_tab_name);
END IF;
END IF;

  Exception
    When others then
         dbms_output.put_line('Error='||sqlerrm);

END gen_insert;
-----------------------------------------------------------------------
BEGIN
   DBMS_OUTPUT.ENABLE(NULL);
   DBMS_OUTPUT.PUT_LINE('SET FEEDBACK OFF');
   DBMS_OUTPUT.PUT_LINE('SET HEADING OFF');
   DBMS_OUTPUT.PUT_LINE('SPOOL 6_static_data.log');
   DBMS_OUTPUT.PUT_LINE('');

   FOR i IN tableList.FIRST..tableList.LAST LOOP
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------- ' );
       DBMS_OUTPUT.PUT_LINE('PROMPT '||to_char(i)||'. Deleting/Inserting into '|| tableList(i)||';' );
       DBMS_OUTPUT.PUT_LINE('DELETE FROM '|| tableList(i)||';' );
       gen_insert(tableList(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Commit;');
    DBMS_OUTPUT.PUT_LINE('SPOOL OFF');

END;
/
SPOOL OFF
PROMPT "Finito...";

Nice work

Thiruppathi, September 26, 2012 - 5:39 am UTC

Hi,

This works very nicely. Except for a case, i have to add the below line to correct an issue.

v_ref_cur_output := REPLACE(v_ref_cur_output,',);',',null);');

Thank you very much.

Thiruppathi

Dynamic insert

amit, October 29, 2012 - 1:29 am UTC

Hi,

I have a requirement where insert is to be done from a dynamic query.

Solution is like


execute immediate ' insert into Amit(APP_ID)
select 1 from '||TABLE_NAME||'';

the above query is working fine.but its doing commit.
But i dnt want a commit.as amit table is temp table,as its doing commit the temp table data is available in other session as well.

Can we have solution without committing.
I tried this way but its not workimg


insert into Amit(APP_ID)
execute immediate 'select 1 from '||TABLE_NAME||'';

Errors: check compiler log
103/8 PL/SQL: ORA-00926: missing VALUES keyword
102/11 PL/SQL: SQL Statement ignored



Tom Kyte
October 29, 2012 - 1:58 am UTC

it is not doing a commit, you are incorrect on that. please explain in detail how you are coming to the conclusion it did a commit??
ops$tkyte%ORA11GR2> create table t (x int);

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          execute immediate 'insert into t(x) select 1 from ' || 'dual';
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from t;

no rows selected



see - rollback = no more row inserted...


and are you really really sure you need dynamic sql - that should be the last path taken in plsql...

Improvement on the script

DIPU V P, January 15, 2013 - 8:20 am UTC

----------------------------------------------
-- Script to generate insert statement dynamically
-- Written by HTH
-- Improved by Zahirul Haque
-- Aug. 29, 2012
----------------------------------------------
This script can be modified to use the insert statement only once for a table and use Select Union all. It will reduce the size of the file.

For example the out put looks like

Insert into tbl_name
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4;

instead of
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;

Tom Kyte
January 15, 2013 - 2:21 pm UTC

I'd prefer the individual inserts and an

alter session set cursor_sharing=force;

alter session set cursor_sharing=exact;



wrapping the script. that way you end up with ONE small statement in your shared pool instead of a monstrosity.


and you won't hit sqlplus limits on the length of things and so on as well.




You want the file to be smaller? don't generate insert statements, just unload data. external tables and/or sqlldr can blow it back in much more efficiently.

Create Dynamic Insert Into

Aakash, May 10, 2013 - 1:40 pm UTC

Step 1:-
Get the output of the below query in a variable:-

select DISTINCT
LISTAGG(DECODE( DATA_TYPE , 'DATE','''TO_DATE(''''''||'||DECODE(DATA_TYPE,'DATE','TO_CHAR('||COLUMN_NAME||',''YYYYMMDD HH24:MI:SS'')',''||COLUMN_NAME||'')||'||'''''', ''''YYYYMMDD HH24:MI:SS'''')''','''''''''||'||COLUMN_NAME||'||'''''''''),'||'',''||') WITHIN GROUP (ORDER BY COLUMN_ID ASC) OVER (PARTITION BY TABLE_NAME)
from all_tab_columns
where table_name = :table
and owner = :owner;

Step 2:-

Replace the value of <variable> with the output of the above sql and run.

SELECT
(
select DISTINCT
'INSERT INTO '||OWNER||'.'||TABLE_NAME||' ('||
LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_ID ASC) OVER (PARTITION BY TABLE_NAME)
|| ')'||CHR(10)
from all_tab_columns
where table_name = :table
and owner = :owner
)
||'VALUES ('
|| <variable>
|| ');'
FROM :table;

Can be done in a single sql query

Gireesh Puthumana, May 21, 2013 - 11:18 am UTC

You can even avoid PL-SQL and can do it using a simple SQL... Well - in two steps.

I have created a sample table (script below) to demonstrate.

CREATE TABLE TEST_TBL1
(
COL1 NUMBER,
COL2 VARCHAR2(10),
COL3 DATE
);

INSERT INTO CONNECTOR.TEST_TBL1 VALUES (1, 'A', TO_DATE('05/21/2013 16:33:08', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (2, 'B', TO_DATE('05/21/2013 16:33:08', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (3, 'C', TO_DATE('05/21/2013 16:33:09', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (4, 'D', TO_DATE('05/21/2013 16:33:09', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (NULL, 'E', TO_DATE('05/21/2013 16:33:25', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (6, NULL, TO_DATE('05/21/2013 16:33:35', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (7, 'G', NULL);
COMMIT;

Now the query is:
----------------
SELECT 'SELECT '''||INS.INSERT_STMT||'('||RECS.SELECT_ROWS||')'' STMT FROM '||RECS.OWNER||'.'||RECS.TABLE_NAME RUN_ME_TO_GET_INSERT_SCRIPT
FROM
(
SELECT ATC.OWNER, ATC.TABLE_NAME, ''||LISTAGG(
CASE
WHEN ATC.DATA_TYPE = 'VARCHAR2' THEN '''||DECODE('||ATC.COLUMN_NAME||', NULL, ''NULL'', ''''''''||'||ATC.COLUMN_NAME||'||'''''''')||'''
WHEN ATC.DATA_TYPE = 'NUMBER' THEN '''||DECODE('||ATC.COLUMN_NAME||', NULL, ''NULL'', '||ATC.COLUMN_NAME||')||'''
WHEN ATC.DATA_TYPE = 'DATE' THEN 'TO_DATE(''''''||TO_CHAR('||ATC.COLUMN_NAME||', ''DD-MON-YYYY HH24:MI:SS'')||'''''', ''''DD-MON-YYYY HH24:MI:SS'''')'
END,
', ') WITHIN GROUP (ORDER BY ATC.COLUMN_ID) SELECT_ROWS
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.TABLE_NAME IN ('TEST_TBL1')
GROUP BY ATC.OWNER, ATC.TABLE_NAME
) RECS,
(
SELECT ATC.OWNER, ATC.TABLE_NAME, 'INSERT INTO '||ATC.OWNER||'.'||ATC.TABLE_NAME||' ('||LISTAGG(ATC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ATC.COLUMN_ID)||') VALUES ' INSERT_STMT
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.TABLE_NAME IN ('TEST_TBL1')
GROUP BY ATC.OWNER, ATC.TABLE_NAME
) INS
WHERE RECS.OWNER = INS.OWNER
AND RECS.TABLE_NAME = INS.TABLE_NAME


This will give you following result:

RUN_ME_TO_GET_INSERT_SCRIPT
---------------------------
SELECT 'INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES ('||DECODE(COL1, NULL, 'NULL', COL1)||', '||DECODE(COL2, NULL, 'NULL', ''''||COL2||'''')||', TO_DATE('''||TO_CHAR(COL3, 'DD-MON-YYYY HH24:MI:SS')||''', ''DD-MON-YYYY HH24:MI:SS''))' STMT FROM CONNECTOR.TEST_TBL1

Just run it to see the magic..

STMT
----
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (1, 'A', TO_DATE('21-MAY-2013 16:33:08', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (2, 'B', TO_DATE('21-MAY-2013 16:33:08', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (3, 'C', TO_DATE('21-MAY-2013 16:33:09', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (4, 'D', TO_DATE('21-MAY-2013 16:33:09', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (NULL, 'E', TO_DATE('21-MAY-2013 16:33:25', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (6, NULL, TO_DATE('21-MAY-2013 16:33:35', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (7, 'G', TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'))


Here is your insert statements.. And yes. It uses LISTAGG - So doesn't work in earlier versions (may be before 10g?)

Hope that helps...

Dynamic insert - multiple tables

Ravi B, May 22, 2013 - 5:40 pm UTC

Hi Tom,

We are trying to solve a unique situation.
We get some third party data which we load into temp tables. We are supposed to consolidate all these tables into one final table.

Following are the rules.

1) final table should have all the columns from all these tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)

In this example, i know the tables, columns etc ,which makes it easy for me to frame the SQL.
In real life, the only information i get is the table names. I have to derive the rest of the information from USER_TAB_COLUMNS.

Question:
How do i frame the following query on the fly based on metadata only?

Example:

DROP TABLE TABLE1;
DROP TABLE TABLE2;
DROP TABLE FINAL_TABLE;

CREATE TABLE TABLE1(HOST_ID VARCHAR(100),DOMAIN VARCHAR(100));
INSERT INTO TABLE1 VALUES(2,'ACME.COM');

CREATE TABLE TABLE2(HOST_ID VARCHAR(100),DOMAIN VARCHAR(100),HOSTNAME VARCHAR(100));
INSERT INTO TABLE2 VALUES(2,'ACME.COM','WS159');
INSERT INTO TABLE2 VALUES(2,'BDNA.COM','FL001');

create table FINAL_TABLE as
select 
   case when table2.host_id is not null then table2.host_id else table1.host_id end as host_id
 , case when table2.domain is not null then table2.domain else table1.domain end as domain
 , case when table2.hostname is not null then table2.hostname else null end as hostname
from table2 
full outer join table1 on table2.host_id=table1.host_id
                       and table2.domain=table1.domain;
                       
select * from FINAL_TABLE;


Tom Kyte
May 22, 2013 - 7:39 pm UTC

I made your example more interesting... but here is the framework.

ops$tkyte%ORA11GR2> CREATE TABLE T1(HOST_ID VARCHAR(10),DOMAIN VARCHAR(10), interesting_column number);

Table created.

ops$tkyte%ORA11GR2> INSERT INTO T1 VALUES(2,'ACME.COM',100);

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO T1 VALUES(2,'ORACLE.COM',42);

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE T2(HOST_ID VARCHAR(10),DOMAIN VARCHAR(10),HOSTNAME VARCHAR(10));

Table created.

ops$tkyte%ORA11GR2> INSERT INTO T2 VALUES(2,'ACME.COM','WS159');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO T2 VALUES(2,'BDNA.COM','FL001');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_sql varchar2(32765) := 'create table ft as select ';
  3      l_on  varchar2(32765);
  4  
  5      cursor c is
  6      select
  7         case when ut1.column_name is not null and ut2.column_name is not null
  8              then 'coalesce( "T1"."' || ut1.column_name || '", "T2"."' || ut2.column_name || '" ) as "' || ut1.column_name || '"'
  9              when ut1.column_name is not null
 10              then '"T1"."' || ut1.column_name || '" as "' || ut1.column_name || '"'
 11              when ut2.column_name is not null
 12              then '"T2"."' || ut2.column_name || '" as "' || ut2.column_name || '"'
 13          end select_list,
 14         case when ut1.column_name is not null and ut2.column_name is not null
 15              then ut1.column_name
 16          end on_list
 17  
 18        from (select * from user_tab_columns where table_name = 'T1') ut1 full outer join
 19             (select * from user_tab_columns where table_name = 'T2') ut2
 20             on (ut1.column_name = ut2.column_name );
 21  begin
 22      for x in C
 23      loop
 24          l_sql := l_sql || x.select_list || ',';
 25          if (x.on_list is not null)
 26          then
 27              l_on := l_on || '"T1"."' || x.on_list || '" = "T2"."' || x.on_list || '" and ';
 28          end if;
 29      end loop;
 30      l_sql := rtrim( l_sql, ',' ) || ' from "T1" full outer join "T2" on (' ||
 31               substr( l_on, 1, length(l_on)-4 ) || ')';
 32  
 33      dbms_output.put_line( l_sql );
 34      execute immediate l_sql;
 35  end;
 36  /
create table ft as select coalesce( "T1"."HOST_ID", "T2"."HOST_ID" )
as "HOST_ID",coalesce( "T1"."DOMAIN", "T2"."DOMAIN" ) as
"DOMAIN","T2"."HOSTNAME" as "HOSTNAME","T1"."INTERESTING_COLUMN" as
"INTERESTING_COLUMN" from "T1" full outer join "T2" on ("T1"."HOST_ID"
= "T2"."HOST_ID" and "T1"."DOMAIN" = "T2"."DOMAIN" )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> desc ft
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 HOST_ID                                      VARCHAR2(10)
 DOMAIN                                       VARCHAR2(10)
 HOSTNAME                                     VARCHAR2(10)
 INTERESTING_COLUMN                           NUMBER

ops$tkyte%ORA11GR2> select * from ft;

HOST_ID    DOMAIN     HOSTNAME   INTERESTING_COLUMN
---------- ---------- ---------- ------------------
2          ACME.COM   WS159                     100
2          BDNA.COM   FL001
2          ORACLE.COM                            42




just remember, if you make this a stored procedure, the owner needs create table granted DIRECTLY to them, not via a role.

Dynamic insert - multiple tables

Ravi B, May 22, 2013 - 11:25 pm UTC

Simply brilliant!!

But what if the number of tables are dynamic too? Meaning, in this example we know there are 2 tables involved. In case of 3 or more tables, forming OUTER JOIN would be tricky isn't it?
Tom Kyte
May 23, 2013 - 2:44 pm UTC

You only get what you ask for, you never said more than two....


your rules are insufficient for more than two tables:

1) final table should have all the columns from all these tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)


what if there are three tables

t1( a,b,c )
t2( a,b,D )
t3( a,b,D )




so, I know the final table has a,b,c,d from #1.

I know the join is only on A,B since those are the common columns.

However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?


your specification needs a lot more work to be finished.

For multiple tables

Gireesh Puthumana, May 23, 2013 - 3:56 pm UTC

If it is okay to assume,

1. Joins should be made on columns which are available on all tables
2. If there is a column which is present in more than one table - but not in all tables, it would be just selected as tab1_colname, tab2_colname etc..

Here you go,

Script to setup test data (added another table)
-------------------------
CREATE TABLE T1(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER);
CREATE TABLE T2(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE));
CREATE TABLE T3(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE), ANOTHER_COLUMN NUMBER);

INSERT INTO T1 (HOST_ID, DOMAIN, INTERESTING_COLUMN) VALUES ('2', 'ACME.COM', 100);
INSERT INTO T1 (HOST_ID, DOMAIN, INTERESTING_COLUMN) VALUES ('2', 'ORACLE.COM', 42);

INSERT INTO T2 (HOST_ID, DOMAIN, HOSTNAME) VALUES ('2', 'ACME.COM', 'WS159');
INSERT INTO T2 (HOST_ID, DOMAIN, HOSTNAME) VALUES ('2', 'BDNA.COM', 'FL001');

INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'ORACLE.COM', 'ABCD', 43);
INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'ACME.COM', 'WS159', 10);
INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'GOOGLE.COM', 'FL001', 11);
COMMIT;


Now just run below query, it will generate the "CREATE TABLE AS SELECT" script for you.


SELECT 'CREATE TABLE T4 AS SELECT '||MAX(SELECT_COLS)||' FROM '||LISTAGG(JOIN_CND, ' ') WITHIN GROUP (ORDER BY TABLE_NAME) NEW_COND
FROM
(
SELECT TABLE_NAME, DECODE(MAX(RHS), NULL, TABLE_NAME, 'FULL OUTER JOIN '||TABLE_NAME||' ON ('||LISTAGG(DECODE(RHS, NULL, NULL, LHS||RHS), ' AND ') WITHIN GROUP (ORDER BY COLUMN_NAME)||')') JOIN_CND,
MAX(SELECT_COLS) SELECT_COLS
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, KEY_COL, LHS, RHS, RWNM,
LISTAGG(DECODE(RWNM, 1, COL_LIST, NULL), ', ') WITHIN GROUP (ORDER BY COL_LIST) OVER (PARTITION BY 1) SELECT_COLS
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, KEY_COL,
MAX(DECODE(KEY_COL, 'K', DECODE(RWNM, 1, TABLE_NAME||'.'||COLUMN_NAME||'=', NULL),NULL)) OVER (PARTITION BY COLUMN_NAME) LHS,
DECODE(KEY_COL, 'K', DECODE(RWNM, 1, NULL, TABLE_NAME||'.'||COLUMN_NAME),NULL) RHS, RWNM,
DECODE(KEY_COL, 'K', 'COALESCE(', NULL) ||
LISTAGG(DECODE(KEY_COL, 'M', TABLE_NAME||'.'||COLUMN_NAME||' '||TABLE_NAME||'_'||COLUMN_NAME, TABLE_NAME||'.'||COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY COLUMN_NAME) OVER (PARTITION BY COLUMN_NAME) ||
DECODE(KEY_COL, 'K', ') '||COLUMN_NAME, NULL) COL_LIST
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, DECODE(COUNT(1) OVER (PARTITION BY COLUMN_NAME), 3, 'K', 1, NULL, 'M') KEY_COL,
ROW_NUMBER() OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME) RWNM, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('T1', 'T2', 'T3')
)
)
)
GROUP BY TABLE_NAME
)

Output:

NEW_COND
--------
CREATE TABLE T4 AS SELECT COALESCE(T1.DOMAIN, T2.DOMAIN, T3.DOMAIN) DOMAIN, COALESCE(T1.HOST_ID, T2.HOST_ID, T3.HOST_ID) HOST_ID, T1.INTERESTING_COLUMN, T2.HOSTNAME T2_HOSTNAME, T3.HOSTNAME T3_HOSTNAME, T3.ANOTHER_COLUMN FROM T1 FULL OUTER JOIN T2 ON (T1.DOMAIN=T2.DOMAIN AND T1.HOST_ID=T2.HOST_ID) FULL OUTER JOIN T3 ON (T1.DOMAIN=T3.DOMAIN AND T1.HOST_ID=T3.HOST_ID)


You need to provide two inputs.
1. List of tables (as 'T1', 'T2', 'T3')
2. Number of tables (you can see "3" in the inner most select)

It uses 5 levels of sub-selects. But yet no joins between ALL_TAB_COLUMNS (no need to join ALL_TAB_COLUMNS n times for n tables). So should be fast.

Hope it helps :)

Dynamic insert - multiple tables

Ravi B, May 23, 2013 - 8:00 pm UTC

Gireesh,

Thank you very much for the SQL. Very elegant and educative! and I am still analyzing how it works for various scenarios.

But to answer Tom's question "However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?"

The final output from your query (T4) should be something like:

SELECT domain, host_id, INTERESTING_COLUMN, 
       t2_hostname AS HOSTNAME,another_column 
  FROM T4 
 WHERE t2_hostname IS NOT NULL
UNION
SELECT domain, host_id, INTERESTING_COLUMN,
       t3_hostname AS HOSTNAME, 
       another_column 
  FROM T4 
 WHERE t3_hostname IS NOT NULL;


Is it possible?


Tom Kyte
May 23, 2013 - 8:15 pm UTC

ok, now I take it up to four tables - with overlapping sets of columns.

and so on.


please - write a detailed SPEC, not this "I'll explain bit by bit, piece by piece - obsoleting what you've already done".

What you have above - with the UNION, does not make sense - not when you get to four tables, five tables, etc. It doesn't even make sense in this case since it creates a DUPLICATE key and so for - everything you've said requires the join keys to be unique - if they are not, you have more explaining to do as to how to process those.

tell us, in english, what the exact specific requirements are.



The extra columns

Gireesh Puthumana, May 24, 2013 - 10:04 am UTC

"However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?"

-In my answer, single row will be selected with two columns T2_D & T3_D (please look at the first two assumptions)

If you want to use them too in the joins, two different rows would be selected (one with D=1 & second with D=2) instead of single row.

Agree 100% to Tom. Union makes no sense at all.

Dynamic insert - multiple tables

Ravi B, May 28, 2013 - 10:42 pm UTC

We are still in the process of developing the system. We are still getting the actual data from our customer as we are doing the development.
The UNION query is just to depict how the final data should look like for the result of the query you provided.

Following is the most realistic data.

1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4

DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;

CREATE TABLE T1(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER);
CREATE TABLE T2(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE));
CREATE TABLE T3(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE), ANOTHER_COLUMN NUMBER);

INSERT INTO T1 VALUES(1,'ACME.COM',100);
INSERT INTO T1 VALUES(2,'ORACLE.COM',42);

INSERT INTO T2 VALUES(1,'ACME.COM','WS159');
INSERT INTO T2 VALUES(3,'BDNA.COM','FL001');

INSERT INTO T3 VALUES(2,'ORACLE.COM','ABCD',43);
INSERT INTO T3 VALUES(1,'ACME.COM','WS158',10);
INSERT INTO T3 VALUES(3,'BDNA.COM','FL001',11);
INSERT INTO T3 VALUES(4,'GOOGLE.COM','G123',NULL);



CREATE TABLE T4(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER,HOSTNAME VARCHAR2(100 BYTE),ANOTHER_COLUMN NUMBER);

INSERT INTO T4 VALUES(1,'ACME.COM',100,'WS159',NULL);    -- row1
INSERT INTO T4 VALUES(1,'ACME.COM',100,'WS158',10);      -- row2
INSERT INTO T4 VALUES(2,'ORACLE.COM',42,'ABCD',43);      -- row3
INSERT INTO T4 VALUES(3,'BDNA.COM',NULL,'FL001',11);     -- row4
INSERT INTO T4 VALUES(4,'GOOGLE.COM',NULL,'G1231',NULL); -- row5


Explanation for each row:

row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3

The answer to the follOWing question you asked earlier, is the same explanation of how rows 1 and 2 above get populated in T4;

Excerpt from your question:

t1( a,b,c )
t2( a,b,D )
t3( a,b,D )

final table has a,b,c,d from #1.

I know the join is only on A,B since those are the common columns.

However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?

Thanks!

RaviB, May 30, 2013 - 10:23 pm UTC

Hi Tom, Gireesh

Any suggestions for me? I am kind of stuck at this point. Please let know what else could be the option? I tried few things by modifying the queries but it is not working out.

Thanks!
Tom Kyte
May 31, 2013 - 3:05 pm UTC

sorry, but you are not very clear.

you have not written down a spec yet.

You want us to reverse engineer some output into a spec. That is not how software development works.

you seem to want us to join T1 to T2 for that first row AND THEN T1 to T3, simply because T2 and T3 have a similar column name but different values. but later, T2 and T3 can join.


what if we have columns:

t1( a, b, d )
t2( a, b, c, d )
t3( a, b, c )

what is the join condition there? what are the DETAILED rules for putting this data together.


I can sort of see it if you assume that T1 is the "lead" table and T2/T3 should be unioned together (but you still have that problem of duplicate keys all of a sudden - YOU WON'T HAVE A PRIMARY KEY in the output!!!!!! of what use is that table???)

but if T1 has an overlap with T2 and T2 has an overlap with T3 - what are the rules for assembling this data???

RaviB, June 05, 2013 - 5:33 am UTC

Tom,

After much deliberation with our business i could get the following from them. This is exact description i got.

"we take the number of columns that are common across all tables at the same time (looking at all tables at the same time) and we use that as the join criteria. Whenever there is a perfect match we merge rows if the other columns had null value. We do not merge whenever there was value on the same non-common-across column"
Tom Kyte
June 05, 2013 - 1:27 pm UTC

... we take the number of columns that are common across all tables at the same
time (looking at all tables at the same time) and we use that as the join
criteria. ...


that is completely different from what you were describing!! totally!


what is a "perfect match"?

and when you have a perfect match - why do other columns have to be null, what point is that trying to make.


software is an engineering practice, or at least it should be. Can you imagine if you got specifications for a bridge or an airplane that were so so so vague?

RaviB - seriously, look at what is typed above and ask yourself if in good faith there exists a developer anywhere on the planet that could write code from that????? Ask yourself honestly.

Try previous query

Gireesh Puthumana, June 05, 2013 - 2:40 pm UTC

If my -Guess- about the requirement is right, that is what exactly the query I gave above does. It uses all common-across-all-tables columns in join and merges the rows which shares common values. Also it does not merge on the not-common-across-tables columns.

Try that and share what different you are looking for.

RaviB, June 06, 2013 - 5:34 am UTC

Hi Tom,

Yes. It is different from what the requirement was before. It is much more simpler now. Earlier, we were complicating the rules too much.

perfect match is "if all the values in common columns match/same".

Hi Gireesh,

Your solution is much closer, but instead of creating two different columns, it should be created as new row if there are different values for the column. That part is the tricky part.

One more headache i have is to convert to SQLSERVER which unfortunately doesn't has equivalent to LISTAGG. It would be a herculean task. My deliverable is by friday :(
Tom Kyte
June 06, 2013 - 1:42 pm UTC

but now, it is not:

... we take the number of columns that are common across all tables at the same
time (looking at all tables at the same time) and we use that as the join
criteria. ...



if you have to create a new row - then you are in effect joining by EVERY SINGLE COLUMN - but you have mismatched sets of columns

t1( a,b,c )
t2( a,b,d )
t3( a,d,e )

for example - you want to join t1&t2 by a,b and then to t3, but t3 joins to t2 by a,d and then t1. it doesn't make sense.


You have not yet, not even close, specified the rules here. "match if all the values in common columns" is the veritable tip of the ice berg. There is more left unsaid here than said.

I'd say, given the inability to spec this out in a document that a coder could develop code from will prevent you from finishing your task by friday.



It is not simpler, it is horribly more complex and less understood than even before.


start over, pretend none of the above conversation exists, pretend I'm a developer that knows nothing about your issue - you are tasking me with developing the process. Now, tell me everything I need to know, provide examples that include all edge cases, provide all information needed to take your idea from idea to code.

A reader, August 21, 2015 - 12:29 pm UTC


Solution doesn't work for numbers with decimals

Dana, June 27, 2017 - 4:12 pm UTC

Hi,
I recently used this solution to dinamicaly generate INSERT statements from different tables and it perfectly worked until I had to apply it on a table where I had a numeric column with decimals.
Let's say we have the following:
TAB_TEST(NUMBER,VARCHAR,NUMBER(10,2))
When I execute the select it returns me an entry like this:
1,'Test1',10,3
The problem is that decimal separator is considered value separator so when I run the INSERT statement I get ORA-00913: too many values. One solution whould be to alter the session and set nls_numeric_characters='.,';

Do you have other suggestions?

Connor McDonald
June 28, 2017 - 12:52 am UTC

You need to remember that this solution was initially from 2008.

Much easier now would be to use SQLCL or SQL Developer, because you just do:

select /* insert */ * from my_table

SELECT /*insert*/* in pl sql?

Robert, May 16, 2019 - 3:31 pm UTC

Thanks a lot for the two different solutions.
I would like to use select /*insert*/* from table in plsql procedure dinamically for each table of schema.

I need an export of all data tables of the schema without sysdba privileges
Chris Saxon
May 17, 2019 - 9:09 am UTC

No - the insert comment is a SQL Developer/SQLcl feature.

You don't need sysdba privileges to export the data. But will you need to meet one of the following criteria:

- To own the tables you're exporting
- Have select privileges on them
- The select any table privilege
- The datapump_exp_full_database to do a schema-level data pump export

If you've got query privileges, you can generate the queries with a statement like:

select 'select /*insert*/* from ' || owner || '.' || table_name
from   all_tables
where  owner = 'CHRIS'

Generate Insert statement dynamically

poshan pandey, May 03, 2021 - 6:16 pm UTC

Hi Zahirul Haque/Team,

This insert statements is working fine for datatypes (Number/varchar/DATE/CHAR etc ) but missing for TIMESTAMP datatype.
Could you please include i.datatype= 'TIMESTAMP' with formats like as DATE column. i tried but insert statement is not generating column for Timestamp columns.
Highly appreciated for such solutions ..Hope you could add some part for TIMESTAMP as well.. It would be great help for me if script can handle for TIMESTAMP data types.

Thanks,
Poshan Pandey
Chris Saxon
May 04, 2021 - 9:54 am UTC

I'm sure you could extend this yourself to include a check for TIMESTAMPs and the appropriate conversions. Go on, give it a try!