Simulate a multi-row table only with the SELECT statement

Question

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

SELECT 'myname' AS 'Name' 

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

how can i get around this to return a single column with multiple values ​​only from select statement

I do not 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 , if possible

Answer 1

If you want to simulate a table with several rows using only the SELECT , this can usually be done using UNION for 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 multiple VALUES in SELECT, for example:

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

Answer 3

If you want to simulate serial data, as in your example. You can define a recursive CTE and use it as a table.

Below code will generate 10 entries

 ;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 the SQL Fiddle http://www.sqlfiddle.com/#!3/d41d8/12452

But CTE cannot be used in several operators. If you need to use it in multiple statements. Then paste the data from the CTE into the Temp Table or Table Variable.