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.
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)
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.
Couldn't you do something like:
select 'Employees' as table_name, name, address
from Employees
where [my_condition]
UNION
select 'Employees_history' as table_name, name, address
from Employees_history
where [my_condition]
It can be achieved Using Table Aliases
SELECT 'Employees' AS [table_name],
name,
address
FROM Employees
WHERE [my_condition]
UNION
SELECT 'History' AS [table_name],
name,
address
FROM Employees_history
WHERE [my_condition]
