Insert Same rows Multiple times
-
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
-
INSERT INTO #insert (Txt)SELECT TxtFROM #Temp TCROSS JOIN(SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns)D(n)WHERE D.n <= T.Cnt -
Cool thanks a lot, you made my day
-
CrazyMan (4/27/2010)
Cool thanks a lot, you made my dayYou are welcome:-)
-
THANK YOU!!! That helped me out immensely!
-
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) -
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
-
durga.palepu (6/18/2016)
You need to define a tally tablecreate 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) -
Thanks, I agree its always a good practice to define primary key on the standard logic tally table.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply