Post reply

Insert Same rows Multiple times

  • April 27, 2010 at 8:13 am

    #232109

    Hi All

    I got stuck in a place where i don't know what to do next,

    CREATE TABLE #Temp (Txt VARCHAR(100),Cnt INT)

    CREATE TABLE #Insert ( Txt VARCHAR(100))

    INSERT INTO #Temp

    ( Txt, Cnt )

    VALUES ( 'Sample', 2)

    INSERT INTO #Temp

    ( Txt, Cnt )

    VALUES ( 'Sample1', 1)

    SELECT * FROM #Temp

    SELECT * FROM #Insert

    On table #Temp we have a txt column and a cnt column, we have to insert this values into #Insert table multiple times as specified in cnt column, as on #insert

    Output from #insert table must be as below

    Sample

    Sample

    Sample1

    Can this be completed, any help on this is much appreciated .

    Cheers

  • April 27, 2010 at 8:55 am

    #1158297

    INSERT INTO #insert (Txt)

    SELECT Txt

    FROM #Temp T

    CROSS JOIN

    (SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns)D(n)

    WHERE D.n <= T.Cnt

  • April 27, 2010 at 9:11 am

    #1158310

    Cool thanks a lot, you made my day 🙂

  • April 27, 2010 at 9:24 am

    #1158318

    CrazyMan (4/27/2010)

    Cool thanks a lot, you made my day 🙂

    You are welcome:-)

  • June 17, 2016 at 7:51 am

    #1885616

    THANK YOU!!! That helped me out immensely!

  • June 17, 2016 at 9:14 am

    #1885649

    djacob 65569 (6/17/2016)

    THANK YOU!!! That helped me out immensely!

    The question now is, do you understand how and why it works?

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

  • June 18, 2016 at 2:02 pm

    #1885752

    You need to define a tally table

    create dbo.numbers(n int);

    Load the numbers table using standard logic available.

    Now to load the data into #insert table,

    INSERT INTO #INSERT(txt)

    SELECT txt

    FROM #TEMP t

    CROSS JOIN dbo.numbers t1

    WHERE t1.n <=t.cnt

  • June 18, 2016 at 2:11 pm

    #1885755

    durga.palepu (6/18/2016)

    You need to define a tally table

    create dbo.numbers(n int);

    Load the numbers table using standard logic available.

    Now to load the data into #insert table,

    INSERT INTO #INSERT(txt)

    SELECT txt

    FROM #TEMP t

    CROSS JOIN dbo.numbers t1

    WHERE t1.n <=t.cnt

    That will also work and is more like I do it. Let's hope they remember to add a Clustered Index to the "standard logic" for building a Tally table because a lot of people forget that.

    Still hope the OP comes back on the question I asked. Lot's of people use this type of thing without know why it works and then can't support it or duplicate it when they need to.

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

  • June 18, 2016 at 2:15 pm

    #1885756

    Thanks, I agree its always a good practice to define primary key on the standard logic tally table.

Viewing 9 posts - 1 through 8 (of 8 total)

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