How to get table name in 'select' expression in SQL Server

Question

I need to make a 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 resulting data will be either in Employees or in 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 , not UNION , as there will be no duplicates in the two branches. Therefore, it can avoid the unnecessary work of removing duplicates in the entire result set.

If there may be duplicates in the branch, add DISTINCT to the individual SELECT (s)

Answer 2

You can add 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 alias , as Martin showed in his answer.

Answer 3

Could 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] 

Answer 4

This 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]