Recursive INSERT query for a grouped sequence

Question

I have two tables:

Table1ID Table2ID Table1ID SomeDate -------- ------------------------------ 1 1 1 2011-01-01 2 2 1 2011-01-02 3 3 2 2011-01-01 4 4 3 2011-01-01 5 3 2011-01-02 6 3 2011-01-03 7 4 2011-01-01 8 4 2011-01-02 

I need to paste the values ​​into a third table, which gives me both the values ​​from table2, as well as the value "sequence", grouped by Table1ID. I tried this, but I always get a value of 1 for the sequence.

 ;WITH Sequences AS ( SELECT t1.Table1ID, MAX(ISNULL(t3.Sequence, 0)) AS [MaxSequence] FROM Table1 t1 LEFT JOIN Table3 t3 (NOLOCK) ON t1.Table1ID = t3.Table1ID GROUP BY t1.Table1ID ) INSERT INTO Table3 ( Table1ID, Table2ID, Sequence ) SELECT t1.Table1ID, t2.Table2ID, s.MaxSequence + 1 FROM Table1 t1 (NOLOCK) JOIN Table2 t2 (NOLOCK) ON t1.Table1ID = t2.Table2ID JOIN Sequences s ON t2.Table1ID = s.Table1ID ORDER BY t2.SomeDate 

I want the result to be like this:

 Table2ID Table1ID Sequence ------------------------------ 1 1 1 2 1 2 3 2 1 4 3 1 5 3 2 6 3 3 7 4 1 8 4 2 

I know that I'm probably missing something stupid simple, but I'm stuck.

Answer 1

 insert into Table3 (Table2ID, Table1ID, Sequence) select Table2ID, Table1ID, row_number() over(partition by Table1ID order by Table2ID) from Table2 

Something to check:

 declare @Table2 table ( Table2ID int identity, Table1ID int ) insert into @Table2 values (1),(1), (2), (3),(3),(3), (4),(4) declare @Table3 table ( Table2ID int, Table1ID int, Sequence int ) insert into @Table3 (Table2ID, Table1ID, Sequence) select Table2ID, Table1ID, row_number() over(partition by Table1ID order by Table2ID) from @Table2 select * from @Table3 

Result:

 Table2ID Table1ID Sequence ----------- ----------- ----------- 1 1 1 2 1 2 3 2 1 4 3 1 5 3 2 6 3 3 7 4 1 8 4 2