How can I add a "custom" row to the top of a select result set?

Question

I can select and pull out a list of records by using a select statement like so with t-sql:

select * from [dbo].[testTable];

But how can I add in a "custom" row to the top of the result set?

For example, if the result set was:

John    [email protected]
Max     [email protected]

I want to add a row, which is not from the table, to the result set so that it looks like so:

Name    Email
John    [email protected]
Max     [email protected]

The reason why I want to do this is because I'm going to export this into a csv file through sqlcmd and I want to add in those "custom row" as headers.

Answer 1

This is the safe way to do this:

select name, email
from ((select 'name' as name, 'email' as email, 1 as which
      ) union all
      (select name, email, 2 as which from [dbo].[testTable]
      )
     ) t
order by which;

In practice, union all will work:

      select 'name' as name, 'email' as email
      union all
      select name, email from [dbo].[testTable]

However, I cannot find documentation that guarantees that the first subquery is completed before the second. The underlying operator in SQL Server does have this behavior (or at least it did in SQL Server 2008 when I last investigated it).

Answer 2

SELECT name, email FROM (
   SELECT 'Name' AS Name, 'Email' AS Email, 1 AS o
   UNION ALL
   SELECT name, email, 2 AS o FROM testTable
) t
ORDER BY o, name

The o column is added to order the result sets of the UNION so that you ensure the first result set appears on top.