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

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 2

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 3

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 4

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 5

easy way...

This exists only one row into the DB

sku = 52 , description = Skullcandy Inkd Green ,price = 50,00

Try to relate another table in which has no constraint key to the main table

Original Query

SELECT  Prod_SKU , Prod_Descr , Prod_Price FROM  dbo.TB_Prod WHERE Prod_SKU = N'52'

The Functional Query ...adding a not related table called 'dbo.TB_Labels'

SELECT TOP ('times')  Prod_SKU , Prod_Descr , Prod_Price FROM  dbo.TB_Prod,dbo.TB_Labels WHERE Prod_SKU = N'52'

Answer 6

In postgres there is a nice function called generate_series. So in postgreSQL it is as simple as:

select information from test_table, generate_series(1, 1000) where id = 5469

In this way, the query is executed 1000 times.

Example for postgreSQL:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; --To be able to use function uuid_generate_v4()

--Create a test table
create table test_table (
   id serial not null,
   uid UUID NOT NULL,
   CONSTRAINT uid_pk PRIMARY KEY(id));

-- Insert 10000 rows
insert into test_table (uid) 
select uuid_generate_v4() from generate_series(1, 10000);

-- Read the data from id=5469 one thousand times
select id, uid, uuid_generate_v4() from test_table, generate_series(1, 1000) where id = 5469;

As you can see in the result below, the data from uid is read 1000 times as confirmed by the generation of a new uuid at every new row.

 id  |uid                                   |uuid_generate_v4
 ----------------------------------------------------------------------------------------
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"5630cd0d-ee47-4d92-9ee3-b373ec04756f"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"ed44b9cb-c57f-4a5b-ac9a-55bd57459c02"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"3428b3e3-3bb2-4e41-b2ca-baa3243024d9"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"7c8faf33-b30c-4bfa-96c8-1313a4f6ce7c"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"b589fd8a-fec2-4971-95e1-283a31443d73"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"8b9ab121-caa4-4015-83f5-0c2911a58640"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"7ef63128-b17c-4188-8056-c99035e16c11"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"5bdc7425-e14c-4c85-a25e-d99b27ae8b9f"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"9bbd260b-8b83-4fa5-9104-6fc3495f68f3"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"c1f759e1-c673-41ef-b009-51fed587353c"
 5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"4a70bf2b-ddf5-4c42-9789-5e48e2aec441"

Of course other DBs won't necessarily have the same function but it could be done:

See here.

Answer 7

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 8

create table #tmp1 (id int, fld varchar(max))
insert into #tmp1 (id, fld) 
values (1,'hello!'),(2,'world'),(3,'nice day!')

select * from #tmp1
go

select * from #tmp1 where id=3
go 1000

drop table #tmp1

Answer 9

Repeat rows based on column value of TestTable. First run the Create table and insert statement, then run the following query for the desired result. This may be another solution:

CREATE TABLE TestTable
(
 ID INT IDENTITY(1,1),
 Col1 varchar(10),
 Repeats INT
)

INSERT INTO TESTTABLE
VALUES ('A',2), ('B',4),('C',1),('D',0)

WITH x AS 
(
  SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER() 
  OVER (ORDER BY [object_id]) 
  FROM sys.all_columns 
  ORDER BY [object_id]
)
SELECT * FROM x
CROSS JOIN TestTable AS d
WHERE x.rn <= d.Repeats 
ORDER BY Col1;

Answer 10

This trick helped me in my requirement.

here, PRODUCTDETAILS is my Datatable and orderid is my column.

declare @Req_Rows int = 12

;WITH cte AS 
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1 FROM cte WHERE Number < @Req_Rows
) 

SELECT PRODUCTDETAILS.* 

FROM cte, PRODUCTDETAILS 
WHERE PRODUCTDETAILS.orderid = 3

Answer 11

I liked very fancy and simple method from another question, that came out useful for me.

Here, simplified, we got cartesian product of 10 initial rows eight times

WITH E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))     AS t(N))
    ,E2 AS (SELECT N = 1 FROM E1 AS a, E1 AS b)
    ,E4 AS (SELECT N = 1 FROM E2 AS a, E2 AS b)
    ,E8 AS (SELECT N = 1 FROM E4 AS a, E4 AS b)
SELECT 'some value'
FROM E8 AS cte

As your understand, you can get any multiplication result by CTE, e.g. 14:

WITH T1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1))     AS t(N))
    ,T2 AS (SELECT N FROM (VALUES (1),(1)) as t(N))
    ,T3 AS (SELECT N = 1 FROM T1 a, T2 b)
SELECT 'some value'
FROM T3 AS cte

Answer 12

In Oracle you can use a heirarchical sub-query and a cross join: -

select     *
from       MyTable m
cross join (select level from dual connect by level <= 1000)
where      m.column = 1234

Answer 13

in sql server try:

print 'wow'
go 5

output:

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

Answer 14

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