Include table name in SQL query

Question

What I need to do is get a table name in result from a union query with 4 selects from different tables. I need to get the id and the table name for further processing.

For example i have table1, table2, table3 table4 and have a query:

SELECT id from table1,blablabla
UNION
SELECT id from table2,blablabla
UNION
SELECT id from table3,blablabla
UNION
SELECT id from table4,blablabla

And I need the result like:

1, blablabla, table1
4, blablabla, table4
7, blablabla, table2

Answer 1

SELECT
    ID,
    'table1' as TableName
FROM
    table1

UNION

...

SELECT
    ID,
    'table4' as TableName
FROM
    table4

Answer 2

SELECT  myid, My otherfield, 'table1' as tablename From table1
UNION
SELECT  myid, My otherfield, 'mytabl2' From table2

Consider a couple of other points, if these are tables that are mutually exclusive use UNION ALL Instead, it will be much much faster. Also, this type of question often comes up when the database design is incorrect, if the same fields are in these tables, why are they not all in one table? Redesign if possible, at least consider if you would be better served doing this. Not knowing what that tables are, I can;t evelauate if they should be in one table, but usually this is a code smell.

Answer 3

Add the table name in your request

SELECT
  id,
  blablabla,
  'table1' as tableName
FROM
  table1
UNION
SELECT
  id,
  blablabla,
  'table2' as tableName
FROM
  table2
 ...