Toad World® Forums

Loop around a temp table and insert into main table

I have a temp table with 4 columns, serial number,date , number of defects,inspector. From that row I would like to insert into the main table x row where X is the number of defects.

e.g Temp Table

Serial Number, Date, Number of defects, Inspector

AAA, 20/03/2015,4, ED

AAB, 20/03/2015,3,JOE

e.g MainTable

Serial Number, Date, Inspector

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAA, 20/03/2015, ED

AAB, 20/03/2015,3,JOE

AAB, 20/03/2015,3,JOE

AAB, 20/03/2015,3,JOE

Any ideas??

Hmmm… this is not a product related question. Still I will give it a shot but not guaranteed to be the best answer.

This is what I will do:

CREATE FUNCTION GenRows(@RowCount INT)

RETURNS @T TABLE (Col1 CHAR(1) NULL)

AS

BEGIN

DECLARE

@Count int

SET @Count = @RowCount

WHILE @Count > 0 BEGIN

 INSERT @T VALUES(NULL)

SET @Count = @Count - 1

END

RETURN

END

SELECT *

FROM #TEMP1 CROSS APPLY GenRows(NumberOfDefects)

And if you don’t want to create a table value function, you may just find a table with more rows than the possible number of defects, like this:

SELECT *

FROM #TEMP1 CROSS APPLY (SELECT TOP(NumberOfDefects) NULL COL1 FROM sys.tables) T

Hope this helps.