[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

How do I display the table name for each record in the result set ? sql Union

Let say i'm doing a union on two or more identical tables...

I want to return all the fields, and also the name of the table the record belongs to.

Example.

Select *,{tablename} FROM table1
UNION ALL
Select *,{tablename} FROM table2

So that the result would show...
field1data, field2data, table1
field1data, field2data, table2
field1data, field2data. table1

This way I know what table the record came from...

I know there has got to be an easy way to do this... because i've seen it done before...I just don't remember how.
0
Ron Malmstead
Asked:
Ron Malmstead
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Select *, 'table1' as tableName  FROM table1
UNION ALL
Select *, 'table2'   FROM table2
0
 
Ron MalmsteadInformation Services ManagerAuthor Commented:
I guess that's easy enough....lol

I had a DUH moment there.

I was thinking there was some function.....to give the name of the db object...but this works just as well.
It's just that I have to concatenate a bit more, while building a statement in my .net code.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>.....to give the name of the db object
there is one unfortunately you have to pass the object_id
0
 
Ron MalmsteadInformation Services ManagerAuthor Commented:
Cool... Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now