# SQL Server repeat rows for n number of times

To repeat each row or record n number of times, based on some column value in a table, we could do it in two ways either by using CTE or if n is less than 2048 then you can do a simple inner join on master.dbo.spt\_values table with a condition where column type = 'P' and check condition on where clause to check the number of rows replicated.

```sql
create table test_one(
	id int identity(1,1) primary key,
	name varchar(50),
	quantity int,
	active bit not null default(1)
);

insert into test_one(name, quantity) values ('product 1', 105), ('product 2', 77);

select * from test_one; -- returns all inserted rows

-- cte query to repeat rows for n number of times.
with test_cte as (
	select id, name, quantity as current_count, 1 as order_count from test_one

	union all

	select
		t2.id, t2.name, (t2.current_count - 1) as current_count, 1 as order_count
	from test_one t1
	join test_cte t2
		on t1.id = t2.id
	where t2.current_count > 1
)

select * from test_cte option (maxrecursion 0);
```

In the above test\_cte in the anchor part, we had done a simple select query to retrieve rows, and along with it, we add order\_count as value 1. It will be always 1 and repeat as many times as the value in current\_count i.e. table's quantity is greater than 1.' In the second part of the CTE query after union all, we perform an inner join on the test\_one table with test\_cte, wherein we check that the test\_cte's current\_count is greater than 1 since we are decrementing the current\_count by 1 each time. This will repeat rows for the given n number of times.