Make SQL Select same row multiple times

Question

I need to test my mail server. How can I make a Select statement that selects say ID=5469 a thousand times.

Answer 1

in sql server try:

print 'wow'
go 5

output:

Beginning execution loop
wow
wow
wow
wow
wow
Batch execution completed 5 times.

Answer 2

If your are doing this in sql Server

declare @cnt int

set @cnt = 0

while @cnt < 1000

begin

   select '12345'

 set @cnt = @cnt + 1

end 

select '12345' can be any expression

Answer 3

You can use the UNION ALL statement.

Try something like:

SELECT * FROM tablename WHERE ID = 5469
UNION ALL
SELECT * FROM tablename WHERE ID = 5469

You'd have to repeat the SELECT statement a bunch of times but you could write a bit of VB code in Access to create a dynamic SQL statement and then execute it. Not pretty but it should work.

Answer 4

If I get your meaning then a very simple way is to cross join on a derived query on a table with more than 1000 rows in it and put a top 1000 on that. This would duplicate your results 1000 times.

EDIT: As an example (This is MSSQL, I don't know if Access is much different)

SELECT
    MyTable.*
FROM
    MyTable
CROSS JOIN
(
    SELECT TOP 1000
        *
    FROM
        sysobjects
) [BigTable]
WHERE
    MyTable.ID = 1234

Answer 5

Create a helper table for this purpose:
JUST_NUMBER(NUM INT primary key)
Insert (with the help of some (VB) script) numbers from 1 to N. Then execute this unjoined query:

SELECT  MYTABLE.*
FROM    MYTABLE,
        JUST_NUMBER
WHERE   MYTABLE.ID = 5469 
    AND JUST_NUMBER.NUM <= 1000

Answer 6

Here's a way of using a recursive common table expression to generate some empty rows, then to cross join them back onto your desired row:

declare @myData table (val int) ;
insert @myData values (666),(888),(777) --some dummy data

;with cte as
(
    select 100 as a
    union all
    select a-1 from cte where a>0 
        --generate 100 rows, the max recursion depth

)
,someRows as
(
select top 1000 0 a from cte,cte x1,cte x2 
       --xjoin the hundred rows a few times
       --to generate 1030301 rows, then select top n rows
)
select m.* from @myData m,someRows where m.val=666

substitute @myData for your real table, and alter the final predicate to suit.

Answer 7

The easy way is to create a table with 1000 rows. Let's call it BigTable. Then you would query for the data you want and join it with the big table, like this:

SELECT MyTable.*
FROM MyTable, BigTable
WHERE MyTable.ID = 5469