populate a temp table with a variable number of copies of a row

Question

Post reply

populate a temp table with a variable number of copies of a row

maroon-78

Ten Centuries

Points: 1014
More actions
February 18, 2011 at 1:10 pm

#247046

I need to create a demo database. I have some SME's entering sample data. I will need to create n copies of each of the rows. Is there a way to do this without using a while command and a counter? I have the number stored in a table so that it can change based on the sample rows ( e.g 100 of the first, 300 of the second, etc)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

INSERT INTO sometable

(column list)

FROM sourcetable st

CROSS JOIN dbo.Tally t

WHERE t.N BETWEEN 1 AND st.yourcountcolumn

Please see the following article for what a Tally Table is and how it replaces Loops.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--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)

maroon-78 Ten Centuries Points: 1014 More actions · Answer 2

Joe,

I just don't see how to do the duplication with a value that varies by row.

UMG Developer SSChampion Points: 13482 More actions · Answer 3

jberg-604007 (2/18/2011)
Joe,
I just don't see how to do the duplication with a value that varies by row.

Did you see the solution that Jeff posted just prior to your last post?