Find out which table a column in a view is from (T-SQL)

Question

I have a SQL view which unions 2 tables together, using a UNION ALL.

I'm writing a stored procedure to update a boolean value, and due to the presence of the union obviously cannot update the view itself. So I'm trying to find the best way to determine which table the row of data came from, and will then update that table directly.

Is there a SYS table or the like which can be used to find this out?

Answer 1

You can add in a column to differentiate between the two tables:

For example:

SELECT table1ID, table1Values...etc, 'table1' As OriginatingTable FROM table1
UNION
SELECT table2ID, table2Values...etc, 'table2' As OriginatingTable FROM table2

Then in your stored procedure, to identify which table to update, simply check the column value for OriginatingTable.