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.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With