Merge multiple column values into one column in different table?

Question

I've done this before, but I don't remember how. I need to select all rows with unique values from several columns in one table, and insert them into one column in a different table. For the destination table they need to be on separate rows in the destination column.

Here's an example:

 Source Table
+-----+------------+------------+------------+------------+
| id | column1 | column2 | column3 | column4 |
+-----+------------+------------+------------+------------+
| 1 | value1 | value1 | value1 | value1 |
+-----+------------+------------+------------+------------+
| 2 | value2 | value3 | value4 | value5 |
+-----+------------+------------+------------+------------+
 Destination Table
+-----+-----------+
| id | column1 |
+-----+-----------+
| 1 | value1 |
+-----+-----------+
| 2 | value2 |
+-----+-----------+
| 3 | value3 |
+-----+-----------+
| 4 | value4 |
+-----+-----------+
| 5 | value5 |
+-----+-----------+

I tried this query but of course it didn't work because the columns aren't equal.

INSERT INTO table2 (column1)
SELECT DISTINCT column1, column2, column3, column4
FROM table1

• EternalHour: I decided to just do one column at a time and the query worked, would still like an answer on doing multiple columns though.
• Racer SQL: Friend, there's something called PIVOT. I'm learning how to use it yet, but you can try it. I see programmers using this here where i work.
• Mark Sinkinson: @RafaelPiccinelli MySQL does not have UNPIVOT which is what you are thinking of.

Answer 1

Try with union

insert into table2(column1)
select column1 from table1
union 
select column2 from table1
union
select column3 from table1

Answer 2

Try this...

SELECT col1,
 col2
FROM
 (SELECT rownum X,col_table1 FROM table1
 ) T1
INNER JOIN
 (SELECT rownum Y, col_table2 FROM table2
 ) T2
ON T1.X=T2.Y;

• RolandoMySQLDBA: Do you have any sample data with output from this query ???
• Andriy M: This probably solves some problem but not the one in this question.
• Rohit Gupta: How does this do the insert ?