Insert last row in a temp table

Question

I have a temp table that spits out bunch of rows as below:

Temp table structure:

create table #final(rownum int,_CID int,Output nvarchar(max))
insert into #final
select rownum,_CID ,Output  from ...and goes the sproc

Final select:

declare @num_records int =  @@ROWCOUNT
if @num_records > 0
select Output from #final

So the Output is:

12345abc  65432  TEST1  2383 JOHN  KIARA     N   987456321456 
12345abc  65432  TEST2  3570 JANE  lastname  A   989746532366 
12345abc  65432  TEST3  3573 KANE  AMBER     N   987454121445 

Requirement: I want to add a last row to the output that shows count of records above.

Desired output:

12345abc  65432  TEST1  2383 JOHN  KIARA     N   987456321456 
12345abc  65432  TEST2  3570 JANE  lastname  A   989746532366 
12345abc  65432  TEST3  3573 KANE  AMBER     N   987454121445
TRAILER   3

Is there a way to do this? I will add a rextester link soon. Any Help?!

Answer 1

You can use UNION after you select the rows, which will add the row to the end of result set returned by the SELECT statement Not after the last row because there is no order which make there is no last row:

SELECT *
FROM #final
UNION
SELECT
'TRAILER',   (SELECT COUNT(1) FROM #final), NULL,NULL,NULL,NULL,NULL,NULL;

Result:

+----------+-------+-------+------+------+----------+------+--------------+
|   Col1   | Col2  | Col3  | Col4 | Col5 |   Col6   | Col7 |     Col8     |
+----------+-------+-------+------+------+----------+------+--------------+
| 12345abc | 65432 | TEST1 | 2383 | JOHN | KIARA    | N    | 987456321456 |
| 12345abc | 65432 | TEST2 | 3570 | JANE | lastname | A    | 989746532366 |
| 12345abc | 65432 | TEST3 | 3573 | KANE | AMBER    | N    | 987454121445 |
| TRAILER  |     3 | NULL  | NULL | NULL | NULL     | NULL | NULL         |
+----------+-------+-------+------+------+----------+------+--------------+