How do I append a row with specific values to the output of my T-SQL query?

Question

I am using SQL Server 2014 and I have the following T-SQL query which runs fine:

SELECT Id, Name from TravelAgency

The last row of the output of this query is shown below:

Id       Name
---      ----
---      ----
715      AB Ltd

I want to "manually" append the following values to the result set of this query: (999, XY Ltd) so that running the modifed query will give me the following results:

Id       Name
---      ----
---      ----
715      AB Ltd
999      XY Ltd

To simplify, the query pulls the data requested from the TravelAgency table and ADDS the specified values at the end of the output. I need to add those specific values in my query but I just can't figure out how to do it!

Reboon · Accepted Answer · 2016-05-19 08:18:20Z

4

Use a union all:

select id, name from xxx -- is your query
union all
select 999 as id, 'XY Ltd' as name

EDIT: In addition to @ThorstenKettner 's comment: If ID is not nummeric or for any reason you can not use it for the sort, then you could do it like this:

select ID, Name
from 
(
    select 1 as SpecialSort, ID, Name from xxx -- is your query
    union all
    select 2 as SpecialSort, 999 as ID, 'XY Ltd' as Name
) AllData
order by SpecialSort asc -- like this your manually added row will appear at the end

edited May 19, 2016 at 8:18

answered May 19, 2016 at 7:46

Reboon

5782 gold badges5 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thorsten Kettner Over a year ago

And if you want the additional record guaranteed to be at the end of the result set as requested, add an appropriate ORDER BY clause - probably simply ORDER BY id provided all real IDs are less than 999.

Arulkumar · Accepted Answer · 2016-05-19 07:58:48Z

3

Use UNION to append your required entry along with return result set. It can avoid if the 999 XY Ltd entry is already exists.

SELECT Id, Name FROM TravelAgency
UNION 
SELECT 999 AS [Id], 'XY Ltd' AS [Name]

If the 999 XY Ltd is already return form result set and allow the duplicate entries you can use UNION ALL and ensure the 999 XY Ltd as last record by ORDER BY

SELECT Id, Name FROM (
    SELECT Id, Name FROM TravelAgency
    UNION ALL
    SELECT 999 AS [Id], 'XY Ltd' AS [Name]
) AS T 
ORDER BY Id

edited May 19, 2016 at 7:58

answered May 19, 2016 at 7:46

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

1 Comment

Thorsten Kettner Over a year ago

You don't need an outer query to apply the ORDER BY. Simply add the ORDER BY clause to your original query.

Collectives™ on Stack Overflow

How do I append a row with specific values to the output of my T-SQL query?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related