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  lt some expression gt  UNION ALL SELECT t2 column  t2 column2 FROM t2  lt some expression gt  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

User · Answer

why dont you add a  source  column to each of the queries with a static value like  select  source 1  as Source  column1  column2    from table1  UNION ALL  select  source 2  as Source  column1  column2    from table2

User · Answer

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

[sql] Add column to SQL query results

Examples related to sql

Examples related to sql-server

Examples related to ssrs-2008