Insert mutliple column vaules into single column in another table

Posted on 2012-09-05
Last Modified: 2012-09-05

I have an extremely large table that I need to get the values of 10 different columns and insert them into a single column in another table. I suppose I could use a UNION query but I'm assuming there is a more efficient way.

As an example, lets say I have a table T1 and I need Col1, Col2 & Col3 data and I need to insert them into another table T2, into a column called Member_ID.

So instead of the UNION query is there something more efficient since I have 10 columns to get and @60 million rows to go through?

Thanks for your time.
Question by:jvoconnell
    LVL 76

    Accepted Solution

    Here are two ways to turn rows into columns.  There are probably more.

    Not sure which way will be faster.  You will just need to test and compare.

    The second way came from here:

    drop table tab1 purge;
    create table tab1(col1 char(1), col2 char(1), col3 char(1));
    insert into tab1 values('a','b','c');
    insert into tab1 values('d','e','f');
    create or replace function myfunc return sys.odcivarchar2list pipelined is
    	for i in (select col1,col2,col3 from tab1) loop
    		pipe row (i.col1); 
    		pipe row (i.col2); 
    		pipe row (i.col3); 
    	end loop;
    show errors
    select * from table(myfunc());
    with nums as (
    select rownum rn from (select 1,2,3 from dual group by cube (1, 2, 3)) where rownum <= 3
    select decode(rn, 1, col1, 2, col2, 3, col3) "colValue"
    from tab1, nums;

    Open in new window

    LVL 1

    Author Closing Comment

    Wow. That's great. Thank you very much. That was fast.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    No problem.  Glad to help.

    It should have been faster but I was at lunch!  ;)

    I was surprised that no other Expert jumped on this one before I got back.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now