Insert mutliple column vaules into single column in another table

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.
LVL 1
jvoconnellAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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:
https://forums.oracle.com/forums/thread.jspa?threadID=251491


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;

Open in new window

0
 
jvoconnellAuthor Commented:
Wow. That's great. Thank you very much. That was fast.
0
 
slightwv (䄆 Netminder) Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.