Home » SQL & PL/SQL » SQL & PL/SQL » Insert Row Data as columns into Another Table (Oracle 10.2.0.3,Windows,Linux)
Insert Row Data as columns into Another Table [message #327567] Mon, 16 June 2008 23:13 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

I have to insert the row data retuned by querying the DBA_CONS_COLUMNS into another table TEST .The Query on DBA_CONS_COLUMNS may return 1 or more columns depending on the constraint name passed.If it is a composite key it will return more than one row as shown below.Now i have to insert this data into a Test Table which has 4 columns.The Query on dba_cons_columns returns only 3 rows based on the constraint passed,so the 4th column in the Test tbale should be left Null.In the second scenario only two rows are retuned,so the COL3 and COL4 of the Test Table shoule be left NULL.Please advice me how to do this Dynamic insert into the Test Table based on the changing constraint Name.


SELECT  TABLE_NAME,COLUMN_NAME FROM DBA_CONS_COLUMNS WHERE CONSTRAINT_NAME=&V_CONSNAME


TABLE_NAME      COLUMN_NAME
==========      ===========
EMP              DEPTNO1
EMP              DEPTNO2
EMP              DEPTNO3

CREATE TABLE TEST(TAB_NAME VARCHAR2(20),COL1 VARCHAR2(20),COL2 VARCHAR2(20),COL3 VARCHAR2(20),COL4 VARCHAR2(20));

TAB_NAME      COL1          COL2      COL3      COL4
========      ====          ====      ====      =====
EMP           DEPTNO1       DEPTNO2   DEPTNO3

CHANGED THE V_CONSNAME VALUE

TABLE_NAME      COLUMN_NAME
==========      ===========
EMP_DET          EMPNO1
EMP_DET          EMPNO2

TAB_NAME      COL1          COL2      COL3      COL4
========      ====          ====      ====      =====
EMP           DEPTNO1       DEPTNO2   DEPTNO3
EMP_DET       EMPNO1        EMPNO2


Any Help is highly appreciated..

Regards
Srini...
Re: Insert Row Data as columns into Another Table [message #327572 is a reply to message #327567] Mon, 16 June 2008 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you have to do this kind of thing dynamically?
A schema is static, objects are part of code, do you dynamically generate your code?

Regards
Michel
Re: Insert Row Data as columns into Another Table [message #327574 is a reply to message #327567] Mon, 16 June 2008 23:49 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

do u know which is the maximum limit for the column,if so,it is possible.

kanish

Re: Insert Row Data as columns into Another Table [message #327576 is a reply to message #327574] Mon, 16 June 2008 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
mm_kanish05 wrote on Mon, 16 June 2008 21:49
do u know which is the maximum limit for the column,if so,it is possible.

kanish



http://www.orafaq.com/forum/t/88153/0/
Please read & follow the Posting Guidelines as presented in URL above

'u' is not a member of this forum
icon7.gif  Re: Insert Row Data as columns into Another Table [message #327580 is a reply to message #327567] Tue, 17 June 2008 00:00 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Michel,

Sorry,if my post is not clear..I just want to insert the data into the Test Table based on the requirement in my earlier post.Let it be dynamic or static...

Regards
Srini...
Re: Insert Row Data as columns into Another Table [message #327582 is a reply to message #327567] Tue, 17 June 2008 00:04 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

possible if you know the Maximum column limit (HINT: MATRIX Query)

Try

kanish
Re: Insert Row Data as columns into Another Table [message #327591 is a reply to message #327567] Tue, 17 June 2008 00:19 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Kanish,

I think this can be done using a stored Proc.Can u please tell me how u can just do it with a query.

Regards
Srini...
Re: Insert Row Data as columns into Another Table [message #327596 is a reply to message #327580] Tue, 17 June 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srinivasocp wrote on Tue, 17 June 2008 07:00
Michel,

Sorry,if my post is not clear..I just want to insert the data into the Test Table based on the requirement in my earlier post.Let it be dynamic or static...

Regards
Srini...

In this case, it is just an "insert select".
The select part is a standard "pivot" query using "decode(max...".

Regards
Michel

Re: Insert Row Data as columns into Another Table [message #327821 is a reply to message #327596] Wed, 18 June 2008 02:08 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi,

The coulmns co11,col2 etc needs to be populated dynamically....Pivot query and Matrix query will definitely not help...This is the Code we have written in order to populate the columns dynamically....Now my problem is resolved...


Copying the code below,so that it would help atleast a few...

CREATE OR REPLACE TYPE con_rec AS OBJECT 
(
 constraint_name VARCHAR2(100),
 column_name     VARCHAR2(100),
 table_name      VARCHAR2(100)
);
/
CREATE OR REPLACE TYPE con_table_type IS TABLE OF con_rec;
/


CREATE OR REPLACE PROCEDURE test_constraints_backup
 IS
   v_sql_str VARCHAR2(1000) := 'SELECT con_rec(constraint_name,COLUMN_name,table_name)
                                  FROM user_Cons_Columns
                                 WHERE table_name LIKE ''' || 'CON%' || '''
                              GROUP BY constraint_name,
                                       COLUMN_name,
                                       table_name
                              ORDER BY constraint_name';
   v_dynamic_cols        VARCHAR2(1000);                             
   v_dynamic_cols_values VARCHAR2(1000); 
   v_col_val             VARCHAR2(100);     
   v_cursor              SYS_REFCURSOR;
   v_cursor2             SYS_REFCURSOR;
   v_insert_str          VARCHAR2(1000);
   v_table_name          VARCHAR2(100);
   con_table             con_table_type;
   v_count NUMBER;
 BEGIN
   OPEN v_cursor FOR v_sql_str;
   FETCH v_cursor BULK COLLECT INTO con_table;
   CLOSE v_cursor;

   FOR cons_name_rec IN (SELECT DISTINCT constraint_name FROM TABLE(con_table))
   LOOP
      --dbms_output.put_line('const distinct : '||cons_name_rec.constraint_name);
      SELECT COUNT(1) INTO v_count FROM TABLE(con_table) 
       WHERE constraint_name  = cons_name_rec.constraint_name;
     --dbms_output.put_line('const name : '||cons_name_rec.constraint_name || ' rows  :' || v_count);
         SELECT DISTINCT table_name 
           INTO v_table_name FROM TABLE(con_table) 
          WHERE constraint_name  = cons_name_rec.constraint_name;
         
         FOR i IN 1..v_count
         LOOP
            v_dynamic_cols := v_dynamic_cols || ',COLUMN'|| i;
         END LOOP;
        dbms_output.put_line('' || v_dynamic_cols);
        
         OPEN v_cursor2 FOR SELECT column_name FROM TABLE(con_table) 
                                WHERE constraint_name = cons_name_rec.constraint_name;
         LOOP          
         FETCH v_cursor2 INTO v_col_val;
            EXIT WHEN v_cursor2%NOTFOUND;
            v_dynamic_cols_values := v_dynamic_cols_values ||  ',' || '''' || v_col_val || '''';
         END LOOP;      
         dbms_output.put_line('' || v_dynamic_cols_values);
         
         v_insert_str := 'INSERT INTO TEST_TABLE (constraint_name,table_name ' || v_dynamic_cols || ')' ||
                         ' VALUES (' || '''' || cons_name_rec.constraint_name || '''' || ',' 
                                     || '''' || v_table_name || '''' || v_dynamic_cols_values || ')';
         dbms_output.put_line('' || v_insert_str);
         v_dynamic_cols := '';
         v_dynamic_cols_values := '';
        
        EXECUTE IMMEDIATE v_insert_str;
   END LOOP;
END;




Regards
Srini...
Re: Insert Row Data as columns into Another Table [message #327823 is a reply to message #327821] Wed, 18 June 2008 02:19 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Pivot query and Matrix query will definitely not help...

You're wrong.

Regards
Michel

Previous Topic: need help in writing proc or getting the logic.
Next Topic: using select statement
Goto Forum:
  


Current Time: Sun Dec 11 00:26:44 CST 2016

Total time taken to generate the page: 0.10541 seconds