Oracle Insert query from a table

Question

I have a table which looks like this:

table1

column1 column2 column3
1       2       3 
a       6       9 
b       8       7 

I need to copy this data to another table in this format:

table2

column_name value
column1     1
column1     a
column1     b
column2     2
column2     6
column2     8
column3     3
column3     9
column3     7

There are more than 100 tables like table1 with millions of rows. I need to copy this in table2.

So, I was planning to write like:

INSERT INTO table2
(column_name, value)
SELECT 'column1', column1
FROM table1;

INSERT INTO table2
(column_name, value)
SELECT 'column2', column2
FROM table1;

INSERT INTO table2
(column_name, value)
SELECT 'column3', column3
FROM table1;

I have 80 columns like this. Is there an efficient way of writing this which also works fast in copying data in Oracle?

Edit: changed example dataset to include different type of data. If I use unpivot, it gives me:

ORA-01790: expression must have same datatype as corresponding expression

Answer 1

Here are a couple of ways of unpivoting the data - one is the manual way, and one is using the UNPIVOT keyword. You'd have to test both to see which one works best for your set of data.

with sample_data as (select '1' column1, 2 column2, 3 column3 from dual union all
                     select 'a' column1, 6 column2, 9 column3 from dual union all
                     select 'b' column1, 8 column2, 7 column3 from dual)
select d.id column_id,
       case when d.id = 1 then sd.column1
            when d.id = 2 then to_char(sd.column2)
            when d.id = 3 then to_char(sd.column3)
       end value
from   sample_data sd
       cross join (select level id
                   from   dual
                   connect by level <= 3) d;

COLUMN_NAME VALUE                                   
----------- ----------------------------------------
          1 1                                       
          1 a                                       
          1 b                                       
          2 2                                       
          2 6                                       
          2 8                                       
          3 3                                       
          3 9                                       
          3 7   

with sample_data as (select '1' column1, 2 column2, 3 column3 from dual union all
                     select 'a' column1, 6 column2, 9 column3 from dual union all
                     select 'b' column1, 8 column2, 7 column3 from dual)
select *
from   (select column1,
               to_char(column2) column2,
               to_char(column3) column3
        from   sample_data) sd
unpivot (value for column_id in (column1 as '1', column2 as '2', column3 as '3')) d;

COLUMN_ID VALUE                                   
--------- ----------------------------------------
1         1                                       
2         2                                       
3         3                                       
1         a                                       
2         6                                       
3         9                                       
1         b                                       
2         8                                       
3         7        

In both queries, I've used the WITH clause (aka subquery-factoring aka CTE) to mimic a table with data in. In your case, you wouldn't need the with clause.

To insert the data into another table, you'd just have to append the insert clause before the sql statement, eg:

insert into other table (column_id, value)
select column_id,
       value
from   (select column1,
               to_char(column2) column2,
               to_char(column3) column3
        from   sample_data) sd
unpivot (value for column_id in (column1 as '1', column2 as '2', column3 as '3')) d;

Answer 2

Use unpivot for that

 select * from table1
    unpivot
    (
    "values" for column_names in (column1,column2,column3))

Edit: Insert statement for above for populate data into another

create table table2
(
column_names varchar2(1),
"values" varchar2(1)
)
/
    insert into table2
    select * from table1
        unpivot
        (
        "values" for column_names in (column1 as '1',column2 as '2',column3 as '3'))

Update It will replace column1 with 1, column2 with 2 etc

 select * from table1
        unpivot
        (
        "values" for column_names in (column1 as '1',column2 as '2',column3 as '3'))