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

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

i'm creating nacha file , if number of records in file not multiple of 10, need insert plenty "dummy" records filled nines (replicate('9',94)) nail next tens place.

i know write loop or perhaps fill temp table 10 records total of nines , select top n. options sense clunky.

i trying think of single select statement me. ideas?

select nacha_rows nacha_table union select replicate('9',94) --do 0 9 times

the formula (10-count(*)%10)%10 tells how many rows add, can select many dummy rows existing dummy table.

select nacha_rows nacha_table union select top (select (10-count(*)%10)%10 nacha_table) replicate('9',94) master.dbo.spt_values

sql sql-server