How to select multi row not from table

Question

I want select multi records for serial number, like :

select * from 
(
    select 11 as COMP_NO
    union
    select 12
    union
    select 13
    union
    select 14
    union
    select 15
) A

If I want to select 100 records or more, Is there a better way ?

What is the table from which you are selecting multiple times in the `UNION`? You might be able to use something like `SELECT * FROM ... WHERE col IN ('l1', 'l2', ...)` — Tim Biegeleisen, Jul 18 '16 at 02:15
I want to insert multi records into an empty table, like initial process. — Robin Li, Jul 18 '16 at 02:23
Possible duplicate of [What is the best way to create and populate a numbers table?](http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) — Ken White, Jul 18 '16 at 02:32
It is very simple table, I want to use t-SQL inert 10 or 100 records. CREATE TABLE dbo.SYSCOMP( COMP_NO varchar(4) NOT NULL, COMP_NAME nvarchar(64) NULL, CONSTRAINT PK_SYSCOMP PRIMARY KEY CLUSTERED ([COMP_NO] ASC) ) ON [PRIMARY] — Robin Li, Jul 18 '16 at 02:35
You mean like `select Comp_No from ( values (11), (12), (13), (14), (15) ) as SerialNumbers( Comp_No )`? — HABO, Jul 18 '16 at 03:05
[Generate a set or sequence without loops](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1) — Vladimir Baranov, Jul 18 '16 at 04:57

score 0 · Answer 1 · answered Jul 18 '16 at 03:12

I find another way to solve my problem, create a user-defined function:

CREATE FUNCTION [dbo].[SerialTable]
(
    @BeginNo int
    , @EndNo int
)
RETURNS TABLE
AS
RETURN
(
    WITH MySerial(SNO, ENO)
    AS(
        SELECT @BeginNo AS SNO, @EndNo as ENO 
        UNION ALL
        SELECT SNO+1, ENO
        FROM MySerial
        WHERE SNO<ENO
    )
    SELECT SNO
    FROM MySerial
)

then can select 10 or more records easily

SELECT * FROM dbo.SerialTable(11, 20);
SELECT * FROM dbo.SerialTable(11, 100);

How to select multi row not from table

1 Answers1