Carven Carven - 24 days ago 9
SQL Question

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

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 john@email.com
Max max@domain.com


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 john@email.com
Max max@domain.com


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

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).

Comments