CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Bilal Ahmed
Bilal Ahmed

Reputation: 245

Insert dummy row in SQL select query

I am working on SQL Server stored procedure. I have a table 'User' having fields (Id, Name, Email,Address).

I have following query returning All Users 

select * from [User]

It returns all users, but I just want to insert following dummy user to resulting record before returning it.

User => Id = 0, Name = "All"

This record will not be present with in the database but while returning result I want to insert this record as first record and then remaining users. I tried to find something like this but in vain.

Upvotes: 7

Views: 23292

Answers (2)

Sarath Subramanian
Sarath Subramanian

Reputation: 21341

You can even set a column numerically to keep the order even though UNION ALL is applied

SELECT 0 RNO, 0 AS Id, 'All' AS Name
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY  Id)RNO, Id, Name 
FROM Users

This will work even though a Zero exists in your column Id(may be a junk value)

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

SELECT 0 AS Id, 'All' AS Name
UNION ALL
SELECT Id, Name FROM Users;

Upvotes: 9

Related Questions