How to repeat/or duplicate my results?

Question

Post reply

How to repeat/or duplicate my results?

clarks

Mr or Mrs. 500

Points: 542
More actions
November 19, 2010 at 8:25 am

#229439

HI All!
This should be a simple one, but I'm having issues coming up with a solution.
I have the following dates Jan 2008-Dec 2012
(i.e. 200801
200802,
etc.)
stored into a table. When I run my query, I simply have 48 rows ( one row for each month). How can I get the 48 rows to repeat, say 100 times?
Thanks!!!:-)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

Your Query

GO 100 🙂

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

clarks Mr or Mrs. 500 Points: 542 More actions · Answer 2

HA! That was simple enough.

Is there a way to make it all one result set, rather than 100 sets. I really need it at least 600 times and don't want to copy and paste 600+ times.:-P

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

MysteryJimbo

SSC-Insane

Points: 24203

November 19, 2010 at 8:55 am

#1253113

Not strictly T-SQL but its a useful feature.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Cross join to a table with the number of rows equal to the number of repetitions you need. Numbers tables are useful for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

clarks Mr or Mrs. 500 Points: 542 More actions · Answer 5

Sorry, can you elaborate a bit more. I'm still not having any luck.

clarks Mr or Mrs. 500 Points: 542 More actions · Answer 6

Nevermind, I got it! I'm a little "Slow" on Friday's. Thanks for the help!:hehe:

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

clarks (11/19/2010)
Nevermind, I got it! I'm a little "Slow" on Friday's. Thanks for the help!:hehe:

That's cool. Would you mind posting your final code so others may learn how to do it? Thanks?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 8

Here is one way of doing it:

DROP TABLE #Temp

DECLARE @X int

SET @X = 1

CREATE TABLE #Temp (Phone varchar(17) null)

WHILE @X < 101

BEGIN

INSERT INTO #Temp(Phone) select Phone from People where Phone = '123-4567'

SET @X = @X + 1

END

select peop.* from People peop

LEFT JOIN #Temp on #Temp.Phone = peop.Phone

where Phone = '123-4567'

--DROP TABLE #Temp (don't forget to clean up)

GSquared SSC Guru Points: 260824 More actions · Answer 9

churlbut (11/22/2010)
Here is one way of doing it:
DROP TABLE #Temp
DECLARE @X int
SET @X = 1
CREATE TABLE #Temp (Phone varchar(17) null)
WHILE @X < 101
BEGIN
INSERT INTO #Temp(Phone) select Phone from People where Phone = '123-4567'
SET @X = @X + 1
END
select peop.* from People peop
LEFT JOIN #Temp on #Temp.Phone = peop.Phone
where Phone = '123-4567'
--DROP TABLE #Temp (don't forget to clean up)

That's very procedural. Why not do it faster, and more efficient:

select Phone

from People

cross join (select top 100 from sys.columns) as Multiplier;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

GSquared (11/22/2010)
churlbut (11/22/2010)
Here is one way of doing it:
DROP TABLE #Temp
DECLARE @X int
SET @X = 1
CREATE TABLE #Temp (Phone varchar(17) null)
WHILE @X < 101
BEGIN
INSERT INTO #Temp(Phone) select Phone from People where Phone = '123-4567'
SET @X = @X + 1
END
select peop.* from People peop
LEFT JOIN #Temp on #Temp.Phone = peop.Phone
where Phone = '123-4567'
--DROP TABLE #Temp (don't forget to clean up)
That's very procedural. Why not do it faster, and more efficient:
select Phone
from People
cross join (select top 100 from sys.columns) as Multiplier;

🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions