Insert Row at top of the table

Question

I have this table

select 
    Store_Desc + ' ('+Store_ID + ')' as storename  
from  
    table_name  
order by
    ID

Results:

 Apple (0051)
 Cherries (0060)
 Banana (0081)

On the top of the result I need another row looks like:

Result:

Fruits
----------------
Apple (0051)
Cherries (0060)
Banana (0081)

How should I need to insert top row with out changing order down (I can use temporary table if needed)

By definition a table is an unordered set. As such the concept of inserting to the top is impossible. Order is only when pulling data from a table. And the way you establish order is with an order by clause. — Sean Lange, Apr 05 '18 at 18:15
@paparazzo yeah. They are asking how to insert a row. Maybe they only want this as output like your answer does. Not really sure. — Sean Lange, Apr 05 '18 at 18:22
Do you need the row in the output, or do you want to insert the row in the table? — The Impaler, Apr 05 '18 at 18:27
Probably a duplicated of https://stackoverflow.com/questions/1456653/sql-server-equivalent-to-oracles-nulls-first — Juan Carlos Oropeza, Apr 05 '18 at 18:27
@paparazzo Yes fruits doesnt have StoreId. anyway that sort technique also worth better than union two queries. — Juan Carlos Oropeza, Apr 05 '18 at 18:48
Maybe this is a better duplicate, now that the question is clearer: https://stackoverflow.com/questions/24771349/how-can-i-add-a-custom-row-to-the-top-of-a-select-result-set @marc_s oh yes, chocolate is my favorite fruit...not too sure about boston though. — Tab Alleman, Apr 05 '18 at 19:36
@paparazzo This shouldnt be part of the database, that is obviously part of the UI. — Juan Carlos Oropeza, Apr 05 '18 at 23:23
@JuanCarlosOropeza If it is obviously part of the UI then how is it a duplicate on sort on null? — paparazzo, Apr 06 '18 at 06:50

paparazzo · Accepted Answer · 2018-04-06T18:04:52.593

3

Unless you use a temporary table would need to repeat ID

select Store_Desc + ' (' + Store_ID +')' as storename, ID  
from table_name  
union all 
select 'fruit', '0'
order BY  ID

or

declare @T table (storename varchar(20), ID varchar(10));
insert into @T values ('fruit', '0');
insert into @T 
select select Store_Desc + ' (' + Store_ID + ')', ID  
from  table_name;
select storename 
from @T 
order by ID;

edited Apr 06 '18 at 18:04

answered Apr 05 '18 at 18:20

paparazzo

44,497
23
105
176

If the OP only wants the row in the output this is an excellent solution to that. – Sean Lange Apr 05 '18 at 18:24

Insert Row at top of the table

1 Answers1