Post reply

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

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

  • February 18, 2011 at 1:31 pm

    #1289044

    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)

  • February 18, 2011 at 1:33 pm

    #1289045

    Joe,

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

  • February 18, 2011 at 4:30 pm

    #1289113

    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?

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

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