[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Insert mutliple column vaules into single column in another table

Posted on 2012-09-05
Medium Priority
498 Views
Experts,

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.
0
Question by:jvoconnell
• 2

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 38369401
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');
commit;

create or replace function myfunc return sys.odcivarchar2list pipelined is
begin
for i in (select col1,col2,col3 from tab1) loop
pipe row (i.col1);
pipe row (i.col2);
pipe row (i.col3);
end loop;
end;
/

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;
``````
0

LVL 1

Author Closing Comment

ID: 38369454
Wow. That's great. Thank you very much. That was fast.
0

LVL 78

Expert Comment

ID: 38369561
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
###### Suggested Courses
Course of the Month17 days, 14 hours left to enroll

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

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