By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--create a dummy table
create table MyTable
(
id bigint not null identity(1,1)
, col1 nvarchar(12)
, col2 nvarchar(12)
, cnt int not null default(1)
)
--create the record to be copied, and a few other records for good measure
insert MyTable (col1, col2, cnt)
values ('row01a', 'row01b', default)
, ('row02a', 'row02b', 2)
, ('row03a', 'row03b', 3)
, ('row04a', 'row04b', default)
, ('row05a', 'row05b', default)
, ('row06a', 'row06b', default)
, ('row07a', 'row07b', default)
, ('row08a', 'row08b', default)
, ('row09a', 'row09b', default)
, ('row10a', 'row10b', default)
, ('row11a', 'row11b', default)
11 rows affected
--example of how we could copy a record N times
declare @rowToBeCopiedId bigint = 5
, @count int = 10
; with noRowsToInsertCte as
(
select 1 x
where @count > 0
union all
select x + 1
from noRowsToInsertCte
where @count > x
)
insert MyTable (col1, col2)
select col1, col2
from MyTable
cross join noRowsToInsertCte
where id = @rowToBeCopiedId
--example output
select min(id) whatIsTheIdForTheOriginalRecord
, col1
, col2
, count(1) howManyOfTheseAreThere
, count(distinct id) proofTheyHaveNewIds
from MyTable
group by col1, col2
order by col1
| whatIsTheIdForTheOriginalRecord | col1 | col2 | howManyOfTheseAreThere | proofTheyHaveNewIds |
|---|---|---|---|---|
| 1 | row01a | row01b | 1 | 1 |
| 2 | row02a | row02b | 1 | 1 |
| 3 | row03a | row03b | 1 | 1 |
| 4 | row04a | row04b | 1 | 1 |
| 5 | row05a | row05b | 11 | 11 |
| 6 | row06a | row06b | 1 | 1 |
| 7 | row07a | row07b | 1 | 1 |
| 8 | row08a | row08b | 1 | 1 |
| 9 | row09a | row09b | 1 | 1 |
| 10 | row10a | row10b | 1 | 1 |
| 11 | row11a | row11b | 1 | 1 |
--example of selecting rows at runtime using the above trick / a count column
--use a view to make this easy to repeat
create view vMyTableWithCount as
with innerCte as
(
select id
, col1
, col2
, cnt
, 1 i
from MyTable
where cnt > 0
union all
select id
, col1
, col2
, cnt
, i + 1
from innerCte
where i < cnt
)
select *
from innerCte
--demo of our view, selecting the first 3 rows according to their counts
select *
from vMyTableWithCount
where col1 in ('row01a', 'row02a', 'row03a')
order by col1, i
option (maxrecursion 10) --You may want to amend the recursion limit for your data, depending how big your cnt values may be
| id | col1 | col2 | cnt | i |
|---|---|---|---|---|
| 1 | row01a | row01b | 1 | 1 |
| 2 | row02a | row02b | 2 | 1 |
| 2 | row02a | row02b | 2 | 2 |
| 3 | row03a | row03b | 3 | 1 |
| 3 | row03a | row03b | 3 | 2 |
| 3 | row03a | row03b | 3 | 3 |