Simulate a table with multiple rows just with SELECT statement

Question

If I can do the following select statement to create a table with one value

 SELECT 'myname' AS 'Name' 

this will return a table with column = Name and one value = myname

how can I work around this to return one column with multiple values from just the select statement

I don't want to do this :

 DECLARE @tmp TABLE (Name varchar(50)) 

 INSERT INTO @tmp (Name) VALUES ('myname1'),('myname2') 
    SELECT * FROM @tmp

Just from a single SELECT statement if possible

Answer 1

If you're wanting to simulate a table with multiple rows just with SELECT statement, this can typically be done with UNION of rows:

SELECT 'myname1' AS 'Name' UNION
SELECT 'myname2' UNION
SELECT 'myname3'
-- etc

Demo: http://www.sqlfiddle.com/#!3/d41d8/12433

Answer 2

Or, you can use the multiple VALUES in the SELECT, like:

SELECT [Name]
FROM (VALUES ('myname1'),('myname2')) AS X([name])

Answer 3

In case you want to simulate sequential data like in your example.You can define a recursive CTE and use it like a table.

Below code will generate 10 records

;With Users as
(
    Select 1 as ID, CAST('Username1' AS varchar(25)) as Name
        union all
    Select ID + 1 , CAST('Username'+CAST(ID+1 AS varchar(5) ) AS varchar(25))
        from Users
        where ID < 10
)
SELECT * FROM Users

Here is SQL Fiddle http://www.sqlfiddle.com/#!3/d41d8/12452

But CTE cannot be used in multiple statements.If you need to use it in multi statements. Then insert data from CTE to Temp Table or Table Variable.