How to get table name within a 'select' statement in SQL Server

Question

I have to make an UNION stament like this more or less:

select [table_name], name, address
from Employees
where [my_condition]

UNION

select [table_name], name, address
from Employees_history
where [my_condition]

The data retrieved will be in either Employees or Employees_history but not in both tables.

I need to know which table the data comes from.

Answer 1

SELECT 'Employees' AS [table_name],
       name,
       address
FROM   Employees
WHERE  [my_condition]
UNION ALL
SELECT 'Employees_history' AS [table_name],
       name,
       address
FROM   Employees_history
WHERE  [my_condition] 

I use UNION ALL rather than UNION as there will be no duplicates across the two branches. So it can avoid some unnecessary work removing duplicates across the whole result set.

If there might be duplicates within branch(es) add DISTINCT to the individual SELECT(s)

Answer 2

You can append a new field as shown below:

select [table_name], name, address, 'Employees'
from Employees
where [my_condition]

UNION

select [table_name], name, address, 'History'
from Employees_history
where [my_condition]

You can also use an alias as Martin has shown in his answer.