ansaurus

Question

Getting data from two different tables into one single table?

Answer 1

+1 A:

select * into table3 from (select * from table1 union select * from table2) as tmp

ennuikiller 2009-09-19 17:42:45

Let's hope bunch of other stuff's have the same schema.

Zed 2009-09-19 17:45:21

Answer 2

+1 A:

This will union both result sets. In order to do a union the columns must be of compatible types.

(SELECT * FROM table1) UNION (SELECT * FROM table2)

Ben S 2009-09-19 17:43:55

Answer 3

+3 A:

The UNION clause will help you here. It combines two or more result sets into one.

Try:

SELECT uid, ABC, OtherStuff
FROM Table1

UNION

SELECT uid, DEF, OtherStuff
FROM Table2

UNION

SELECT uid, GHI, OtherStuff
FROM Table3

ORDER BY uid

There is a variation on this theme with the UNION ALL operator. UNION will explicitly remove duplicate rows while UNION ALL retains them.

This has ramifications beyond the simple difference in rows: to remove duplicates the UNION operator needs to sort the final result set. This is overhead the UNION ALL operator does not experience. Moreover, the explicit sort operation may cause a result set collected by UNION to differ in sort order when compared to UNION ALL. I suggest you use an explicit ORDER BY statement after the result set is collected to ensure your sort order is as you intend it to be.

Please also be aware that the number of columns must match in the result sets being UNIONed. It's not clear from the OP how the two tables differ in terms of the number of columns they store, and so I am hesitant to do a UNION between two SELECT * statements.

David Andres 2009-09-19 17:44:10

If you want to create a real table from this, the exact statement depends on the database you're using. On most, it's "create table foo as select uid, ABC..."

KingPong 2009-09-19 17:47:18

@KingPong: I took the OP to mean that he/she wanted a single result set, not necessarily a real table. I've posted a comment to the OP because you could be right.

David Andres 2009-09-19 17:52:15

Answer 4

A:

Keep in mind that the union command will remove duplicate rows. If that is not desired behavior, you want to use union all instead.

Chi 2009-09-19 19:09:37

ansaurus

tags:

views:

answers:

Getting data from two different tables into one single table?

related questions