-1

I have an old table with 3 columns (COLUMN01, COLUMN02, COLUMN03) and I need to migrate to a new table which contains 1 column (VALUE) how to do? I tried a Union but it does not work ...

GMB
  • 216,147
  • 25
  • 84
  • 135
Prometheus
  • 3
  • 6
  • 1
    Show us some sample data from the old table, and how you want that data in the new table. (As formatted text, no images.) Indeed, UNION ALL seems like an option. – jarlh Nov 04 '20 at 08:48
  • Please tag your question with the database you are running: mysql, oracle, postgresql...? – GMB Nov 04 '20 at 08:55
  • For example : COLUMN01 = "data1", COLUMN02 = "data2", COLUMN03 = "data3" and i would like put the data on the COLUMN Valeur – Prometheus Nov 04 '20 at 08:55
  • Aside from the technical solution to your question, what you propose raises a huge red flag on your data design. Typically we want to design our tables to Third Normal Form. But what you propose violates that design rule. – EdStevens Nov 04 '20 at 13:09

2 Answers2

0

A generic approach uses insert ... select and union all:

insert into newtable (value)
select column01 from oldtable
union all select column02 from oldtable
union all select column03 from oldtable

In Oracle, this is more efficiently done with cross apply, so the table is scanned only once instead of three times:

insert into newtable (value)
select v.col 
from oldtable o
cross apply (
    select o.column01 col from dual
    union all select o.column02 from dual
    union all select o.column03 from dual
) v
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Note that Oracle also supports the standard `cross join lateral`, instead of the non-standard `cross apply` –  Nov 04 '20 at 09:10
0

if you like to combine the columns in the old table and migrate the combined values into one single column in the new table, you can do the following.

INSERT INTO NEW_TABLE (VALUE)
SELECT CONCAT(COLUMN01, " ", COLUMN02, " ", COLUMN03)
FROM OLD_TABLE;
JEEVA RAJA PAUL
  • 59
  • 3