Select from multiple tables and put them in a temporary table

Question

I need to create a temporary table and put data from multiple tables in it. In this question they all select from one table. how to select from multiple tables? (All tables have exactly same columns)

Edit: tables have no relationship and they are 9 tables, but they all have same columns like id,username,password. each table contains information about specific users.

Then the only option I see here would be to UNION together the tables, but that would only make sense if they have the same definition. — Tim Biegeleisen, Jul 18 '17 at 05:08

cosinepenguin · Accepted Answer · 2017-07-18T05:29:01.850

Union can help you combine those two tables, and then you can use the TEMPORARY method!

CREATE TEMPORARY TABLE IF NOT EXISTS tableTemp AS (SELECT * FROM table1 UNION SELECT * FROM table2)

This would result in data from both your tables being "unified" into one table.

So with two tables like this:

table1

testCol | testCol2
--------+---------
   A    |     1

table2

testCol | testCol2
--------+---------
   B    |     2

the code above would return

tableTemp

testCol | testCol2
--------+---------
   A    |     1
   B    |     2

Hope it helps!

EDIT

Just to be clear, the code above was just an example! You would need to change variable names:

CREATE TEMPORARY TABLE IF NOT EXISTS tableTemp AS (SELECT * FROM [[NAME OF FIRST TABLE]] UNION SELECT * FROM [[NAME OF SECOND TABLE]])

Since you say you have 9 tables, you would just need to keep appending a UNION SELECT for each of those tables. For example:

CREATE TEMPORARY TABLE IF NOT EXISTS tableTemp AS (SELECT * FROM [[NAME OF FIRST TABLE]] UNION SELECT * FROM [[NAME OF SECOND TABLE]] UNION SELECT * FROM [[NAME OF THIRD TABLE]] UNION SELECT * FROM [[NAME OF FOURTH TABLE]] UNION SELECT * FROM [[NAME OF FIFTH TABLE]] UNION SELECT * FROM [[NAME OF SIXTH TABLE]] UNION SELECT * FROM [[NAME OF SEVENTH TABLE]] UNION SELECT * FROM [[NAME OF EIGTH TABLE]] UNION SELECT * FROM [[NAME OF NINTH TABLE]])

you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT * FROM table2)' at line 1 — eylay, Jul 18 '17 at 05:24
Well you would need to change the name of the tables you are selecting from... `table2` was only in my test! :) — cosinepenguin, Jul 18 '17 at 05:25
This seems like it should work, but it looks like you actually can't create a temp table directly from a union. It returns the error that @eylay reported here in the comments. This answer provides a workaround to the same problem: https://stackoverflow.com/a/31751787/3508398 — Kylok, Oct 29 '19 at 05:57

score 0 · Answer 2 · answered Jul 18 '17 at 05:09

Try using joins and unions. And since u want temporary table alias will do. For join, query will be as:

Select column_names from table1 join table2 on column_names

If you have more than 2 table, you can use chain of joins to get results.

See this link https://www.google.co.in/url?sa=t&source=web&rct=j&url=https://www.w3schools.com/sql/sql_join.asp&ved=0ahUKEwim456hiJLVAhUIVbwKHQ9SBzwQFggeMAA&usg=AFQjCNH2uk_VTd4skktAbnIq3dv1MlKD-g

score 0 · Answer 3 · answered Jul 18 '17 at 06:32

CREATE TABLE IF NOT EXISTS table_3 AS SELECT 1st_table_id as union_id, 1st_table_username as union_username, 1st_table_password as union_password FROM table_1 UNION SELECT 2nd_table_id as union_id, 2nd_table_username as union_username, 2nd_table_password as union_password FROM table_2

Change the "union_id", "union_username" and "union_password" as per the new column name of the new table.

score 0 · Answer 4 · answered Oct 29 '19 at 06:10

This is a bit late, but the code in the accepted answer here results in a syntax error.

As pointed out in this answer, you can't create a table (or a temp table) directly from a union. You can stick the union in a sub-query though, like this:

CREATE TEMPORARY TABLE IF NOT EXISTS tableTemp AS (
    SELECT *
    FROM (
        SELECT * FROM table1
        UNION
        SELECT * FROM table2
    )
)

Select from multiple tables and put them in a temporary table

4 Answers4