Add a value to each line returned from a union


I'm creating a CTE using two select statements and a UNION of two tables.  I would like to add a "faux" column to the results to identify  which table each row came from.

Something like:

    SELECT field1, field2, field3, 't1' as Source
     FROM table1
     SELECT  field1, field2, field3, 't2' as Source
     FROM table2

The results would look like:
field1 | field2 | field3 | Source
   A     |      B    |     C     |     T2
   C     |      R    |     G     |     T1

Any idea how I can do that?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
I would like to add a "faux" column to the results to identify  which table each row came from.

I thought you already derived a "Source" column in your cte ? it should generating the result as what you have been shown in your question.
Jim HornSQL Server Data DudeCommented:
It appears that you have already done it.  As long as all queries in a UNION have the same schema you can populate columns however you wish,  either with table.column values or descriptive hard-coded values.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Since there will always be a difference with the "faux" columns, I would use union all to improve performance since a sort would not be required.
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

ttist25Author Commented:
Hey guys - you are right.  What I failed to include in my example query was that I was using SELECT DISTINCT blah in my UNION queries which was pointless in this situation.  The distinct is what was preventing me from adding the Source column alias.  

Thanks for the responses and sorry for the delay in mine.  Been getting my butt kicked on something else.  

PS - Jim - what is the trophy in your new pic?
Jim HornSQL Server Data DudeCommented:
It's a foot-and-a-half knockoff of the NHL's Stanley Cup that is awarded every year for their championship team.  

I play in the Adult Hockey Association, which is in the Twin Cities of Minnesota and has 180+ amateur adult hockey (aka Beer League) teams spread across ten divisions.  Last year my Wood Haulers team won the championship in our division, so after the championship game we were awarded the cup, and just like the professionals we all make silly 'We're Number One!' poses with it.   We took a 2-2 tie into overtime and a shootout, and as the goalie I got us to a shootout win after eight shots.   The picture was taken in the locker room after the game.  

After that roller coaster of emotion which capped off a four-day tournament I may have had three working brain cells.
ttist25Author Commented:
Eight shots denied???!  Nice job!!!!!

I'm finding that amateur hockey is a relatively popular past time among many of the "data folks" I work with.  

Wonder what that says about us?  :D

Jim HornSQL Server Data DudeCommented:
>Wonder what that says about us?  :D
I think my neighbor nailed that one.  He thinks our fathers had mostly manual labor jobs so when they finished their work day they were exhausted and wanted to drink and relax, so golf and softball were the big sports.  Our generation has mostly sit-down, less-than-social jobs, so when we finish our work day we want to tell raunchy jokes to our buddies and then go out and hit something.
ttist25Author Commented:
Yep.  Nailed it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.