Daily Archives: Sep 21, 2014


Generate Defined number of rows dynamically (SET based approach)

In this article , we will generate as much number of rows as needed for each row of given table dynamically. We have a table named #Employee with two columns “EmpName” and “RowsToGenerate”. Lets explore “Generate rows dynamically” topic in with examples and demos.

Generate Rows dynamically as per given count:

Below is the structure of the table #Employee


CREATE TABLE #Employee

(

EmpName VARCHAR(100),

RowsToGenerate INT

)

Now put some demo values in this table as below;


INSERT INTO #Employee(EmpName, RowsToGenerate)

VALUES('Emp1', 5)

,('Emp2', 10)

,('Emp3', 4)

,('Emp4', 1)

,('Emp5', 2)

,('Emp6', 8)

,('Emp7', 1)

,('Emp8', 0)

,('Emp9', 0)

,('Emp10', 0)

Trick

I will create a CTE (Common table expression) with serial numbers to perform a join on #Employee table and this created CTE to generate the rows as much as we want as per column value “RowsToGenerate”. In case of 0, it will not generate any row.

Have … More