Create table as ( using a column and table name)

mysql, sql

Solution

Include a quoted string literal. Be sure to also give it a column alias, which will be used as the column name in the resultant table.

CREATE TABLE teams AS 
  /* Quoted string literal with column alias */
  (SELECT name, 'table1' AS `tablename` FROM table1)
  UNION 
  (SELECT name, 'table2' AS `tablename` FROM table2)
  UNION
  (SELECT name, 'table3' AS `tablename` FROM table3);

Note that since you are in effect adding a second value to each row which differentiates it from potentially similar rows in the other tables, the `UNION` is now the eqivalent of a `UNION ALL`, and duplicate rows won't be de-duped as the plan `UNION` would have. Just beware, that the results may differ from what your original `UNION` produced.

Problem

I need to build a table from several tables in MySql and I want it has two columns like: ``` ------------------- name | table_name | ------------------- ``` I'm doing this: ``` Create table teams as ( Select name from table1 union Select name from table2 union Select name from table3); ``` How could I include each table name as a second column?? Thank you!

Original source