Own values in a sql query

Question

I'm using this sql query:

SELECT     id, datetime
FROM         (SELECT     id, datetime
                       FROM          DanskAktieAnalyse.dbo.vTest
                       UNION ALL
                       SELECT     id, datetime
                       FROM         vTest2) AS articles
ORDER BY datetime

Is it possible to get an extra attribute to determin which of the two selects in the union the row comes from? Example 1 if from vTest and 2 if from vTest2 ?

Answer 1

Yes.

SELECT     id, datetime, 1 AS ColumnName

Where ColumnName is the name you want for these values.

So the full query would be

SELECT     id, datetime
FROM         (SELECT     id, datetime, 1 AS ColumnName
                       FROM          DanskAktieAnalyse.dbo.vTest
                       UNION ALL
                       SELECT     id, datetime, 2 AS ColumnName
                       FROM         vTest2) AS articles
ORDER BY datetime

Answer 2

SELECT     id, datetime, tname
FROM         (SELECT     id, datetime, 'vTest' as tname
                       FROM          DanskAktieAnalyse.dbo.vTest
                       UNION ALL
                       SELECT     id, datetime, 'vTest2' as tname
                       FROM         vTest2) AS articles
ORDER BY datetime

Answer 3

yes, just add an expression to the query output:

  Select  id, datetime, source
  From (Select id, datetime, 'vtest' source 
        From DanskAktieAnalyse.dbo.vTest 
        Union All
        Select id, datetime, 'vtest2' source 
        From vTest2) AS articles 
  Order By datetime 

and you really don't need the separate outer query

  Select id, datetime, 'vtest' source 
  From DanskAktieAnalyse.dbo.vTest 
    Union All
  Select id, datetime, 'vtest2' source 
  From vTest2
  Order By datetime

should work just fine