How can I duplicate the records of table1 and store them in table2 along with the progressive number calculated from startnum and endnum? Thanks
the first row must be duplicated in 4 records i.e num: 80,81,82,83
x
Startnum | Endnum | Data
---------+-------------+----------
80 | 83 | A
10 | 11 | C
14 | 16 | D
Result:
StartEndNum | Data
------------+-----------
80 | A
81 | A
82 | A
83 | A
10 | C
11 | C
14 | D
15 | D
16 | D
Advertisement
Answer
Without recursion:
declare @t table(Startnum int, Endnum int, Data varchar(20))
insert into @t values
(80, 83, 'A'),
(10, 11, 'C'),
(14, 16, 'D');
select a.StartEndNum, t.Data
from @t t cross apply (select top (t.Endnum - t.Startnum + 1)
t.Startnum + row_number() over(order by getdate()) - 1 as StartEndNum
from sys.all_columns) a;
You can use any other table with enough rows instead of sys.all_columns