CODEWITHSUNDEEP
sqlsql-servert-sql
anmarti
anmarti

Reputation: 5143

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

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.

Upvotes: 6

Views: 31818

Answers (4)

Martin Smith
Martin Smith

Reputation: 453212

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)

Upvotes: 12

Vishal Suthar
Vishal Suthar

Reputation: 17194

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]

Upvotes: 0

brianestey
brianestey

Reputation: 8302

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]

Upvotes: 0

Darren
Darren

Reputation: 70728

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.

Upvotes: 1

Related Questions