Add column to SQL query results

Question

I'm putting together a report in SSRS. The dataset is populated with a SQL query of an MS SQL server. It's querying several similar tables using Union All. The problem is that there's some information loss. The different tables are for different worksites, but none of the columns in those tables has the name of the site; the only way to identify a site is by the table name. In the combined columns which are the result of the Union All, there's no way to tell which rows come from which site.

Is there a way to alter my query to add a column to the results, which would have the worksite with which each row is associated? I can't add this to the original table, because I have read-only permissions. I'd thought of something like this, but I don't know what sort of expression to use, or if it can even be done:

SELECT t1.column, t1.column2 FROM t1 <some expression> UNION ALL SELECT t2.column, t2.column2 FROM t2 <some expression> UNION ALL ... 

etc. The expression would 'add' a column, which would add the site name associated with each part of the query. Could this or anything else work to get the site name?

Answer 1

Manually add it when you build the query:

SELECT 'Site1' AS SiteName, t1.column, t1.column2 FROM t1  UNION ALL SELECT 'Site2' AS SiteName, t2.column, t2.column2 FROM t2  UNION ALL ... 

---

EXAMPLE:

DECLARE @t1 TABLE (column1 int, column2 nvarchar(1)) DECLARE @t2 TABLE (column1 int, column2 nvarchar(1))  INSERT INTO @t1 SELECT 1, 'a' UNION SELECT 2, 'b'  INSERT INTO @t2 SELECT 3, 'c' UNION SELECT 4, 'd'   SELECT 'Site1' AS SiteName, t1.column1, t1.column2 FROM @t1 t1  UNION ALL SELECT 'Site2' AS SiteName, t2.column1, t2.column2 FROM @t2 t2 

RESULT:

SiteName  column1  column2 Site1       1      a Site1       2      b Site2       3      c Site2       4      d