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

Can you use the code tags, and show us exactly what you are after? — JNK
– JNK, Commented Sep 21, 2010 at 13:49
All the answers below (right now) suggest to hardcode it as a string. Other things I've looked up talk about passing it as a variable to dynamic SQL. What about automatically generating it? Is there something like @@SERVERNAME? SELECT tablealias.@@TABLENAME FROM Table1 tablealias — undrline - Reinstate Monica
– undrline - Reinstate Monica, Commented Mar 16, 2023 at 15:07

devnull · Accepted Answer · 2010-09-21 13:50:04Z

26

SELECT
    ID,
    'table1' as TableName
FROM
    table1

UNION

...

SELECT
    ID,
    'table4' as TableName
FROM
    table4

answered Sep 21, 2010 at 13:50

devnull

2,86024 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HLGEM · Accepted Answer · 2010-09-21 13:53:21Z

3

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.

answered Sep 21, 2010 at 13:53

HLGEM

97k15 gold badges120 silver badges191 bronze badges

1 Comment

Alakdae Over a year ago

Thanks for the information. At beginning it was one table, but new data come in other categories and the tables are now for different categories and hold similar data, but for security reason should be kept separate. Only this one query accesses more then one table and it's only for listing.

Julien Hoarau · Accepted Answer · 2010-09-21 13:50:55Z

2

Add the table name in your request

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

answered Sep 21, 2010 at 13:50

Julien Hoarau

50.2k20 gold badges134 silver badges117 bronze badges

1 Comment

Alakdae Over a year ago

Thanks! Exacly what I needed but devnull was first so I accept his answer.

Collectives™ on Stack Overflow

Include table name in SQL query

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related