Solved

Get the table name in Union Select too?

Posted on 2014-04-28
4
502 Views
Last Modified: 2014-04-28
Hello,

I am trying to query several tables that have the same 3 columns and combine the results like this.

Select col1,col2,col3 from tblA
union
Select col1,col2,col3 from tblB
union
Select col1,col2,col3, from tblC

From
The end result who not only show me the union of three columns form all the tables but also - which is there source table as well like this

Col1 | col2 | col3 | myTableName
Er | ty | rt } tblA
Er | ty | rt } tblB
0
Comment
Question by:Rayne
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
plusone3055 earned 400 total points
ID: 40027579
yes
Select col1,col2,col3, Tbla as tablename  from tblA
union all
Select col1,col2,col3, tblb as tablename from tblB
union all
Select col1,col2,col3, tblc as tablename from tblC
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 40027580
Select col1,col2,col3, 'tblA' AS table_name from tblA
union all
Select col1,col2,col3, 'tblB' from tblB
union all
Select col1,col2,col3, 'tblC' from tblC
order by col1, col2, col3
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027583
hehehe I won by 11 seconds LOL
0
 

Author Closing Comment

by:Rayne
ID: 40027828
thank you both :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now