How to return N records in a SELECT statement without a table

Question

I am creating a NACHA file, and if the number of entries in the file is not a multiple of 10, we need to insert enough "dummy" entries filled with nines ( replicate('9',94)) to get to the next tenth place.

I know that I could write a loop or maybe fill a temp table with 10 entries filled with nines and select the top part of N. But these parameters seem awkward.

I tried to come up with one select statement that could do this for me. Any ideas?

select nacha_rows
from NACHA_TABLE
union all
select replicate('9',94)  --do this 0 to 9 times

Answer 1

(10-COUNT(*)%10)%10 , , .

SELECT nacha_rows
FROM NACHA_TABLE
UNION ALL
SELECT TOP (SELECT (10-COUNT(*)%10)%10 FROM NACHA_TABLE) REPLICATE('9',94)
FROM master.dbo.spt_values

Answer 2

. 9 . modulo . . , .

;WITH dummydata (num, nines) 
 AS (SELECT 1 AS num, Replicate('9', 94) 
     UNION ALL 
     SELECT num + 1, Replicate('9', 94) 
     FROM   dummydata 
     WHERE  num < 9) 

SELECT * 
FROM   nacha_table 
UNION ALL 
SELECT nines 
FROM   dummydata 
WHERE  num >= CASE 
            WHEN (SELECT Count(1) % 10 FROM nacha_table) = 0 THEN 10 
            ELSE (SELECT Count(1) % 10 FROM nacha_table) 
          END 

Answer 3

, 9 -, , , JChao,

With Filler AS (
  SELECT n.n, replicate('9',94) nacha_rows
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(n)
)
SELECT nacha_rows
FROM   NACHA_TABLE
UNION ALL
SELECT nacha_rows
FROM   Filler
       OUTER APPLY (SELECT count(1) % 10 last
                    FROM   NACHA_TABLE) l 
WHERE  filler.n + l.last <= 10
  AND  l.last > 0 -- to prevent filler line when NACHA_TABLE has exactly 10x rows

SQLFiddle

Answer 4

, :

select '1' as [col1], 'abcdef' as [col 2]
union all 
select '2' as [col1], 'abcdef' as [col 2]
union all 
select '3' as [col1], 'abcdef' as [col 2]
union all 
select '4' as [col1], 'abcdef' as [col 2]

Answer 5

10 ;

;with T(ord) as
(
    select 1 as ord union all select ord + 1 from T where ord < 10
)
select isnull(nacha_rows, replicate('9', 94)) from T left join (
    select  
        ROW_NUMBER() over (order by nacha_rows) row, nacha_rows
    from NACHA_TABLE
) T2 on row = ord

Edit ; I just realized that, of course, the table can have> 10 rows, first of all, in this case it will not work.