求SQL语句，疑难！ 数据更新
如下：
表t1
s   r   t
1   1   a
1   2   b
1   3   c
1   4   d
1   5   e
1   6   f
在这个基础上还想问个问题，（还是那个表）先插入一条，两条……
保持r列数据的连续性
插入(在另一个表中t2)

s   r   t
1   2   g
1   2   l
1   4   h
结果：
s   r   t

1   1   a
1   2   g
1   3   l
1   4   b
1   5   c
1   6   h
1   7   d
1   8   e
1   9   f
请教！谢谢！

------解决方案--------------------
Create Table t1
(s Int,
r Int,
t Varchar(10))
Insert t1 Select 1, 1, 'a '
Union All Select 1, 2, 'b '
Union All Select 1, 3, 'c '
Union All Select 1, 4, 'd '
Union All Select 1, 5, 'e '
Union All Select 1, 6, 'f '

Create Table t2
(s Int,
r Int,
t Varchar(10))
Insert t2 Select 1, 2, 'g '
Union All Select 1, 2, 'l '
Union All Select 1, 4, 'h '
GO
Select *, 1 As Flag Into #T1 From T1
Insert #T1 Select *, 2 From T2

Select ID = Identity(Int, 1, 1), * Into #T2 From #T1 Order By r, Flag Desc

Delete From T2

Insert T2 Select s, ID, t From #T2

Select * From T2

Drop Table #T1, #T2
GO
Drop Table T1, T2
/*
s r t
1 1 a
1 2 g
1 3 l
1 4 b
1 5 c
1 6 h
1 7 d
1 8 e
1 9 f
*/

------解决方案--------------------
--下面是将数据放入T2表中的代码.

if object_id( 'pubs..t1 ') is not null
drop table t1
go
create table t1(s varchar(10),r int,t varchar(10))
insert into t1(s,r,t) values( '1 ', 1, 'a ')
insert into t1(s,r,t) values( '1 ', 2, 'b ')
insert into t1(s,r,t) values( '1 ', 3, 'c ')
insert into t1(s,r,t) values( '1 ', 4, 'd ')
insert into t1(s,r,t) values( '1 ', 5, 'e ')
insert into t1(s,r,t) values( '1 ', 6, 'f ')
insert into t1(s,r,t) values( '2 ', 1, 'x ')
insert into t1(s,r,t) values( '2 ', 2, 'y ')
insert into t1(s,r,t) values( '2 ', 3, 'z ')
go

if object_id( 'pubs..t2 ') is not null
drop table t2
go
create table t2(s varchar(10),r int,t varchar(10))
insert into t2(s,r,t) values( '1 ', 2, 'g ')
insert into t2(s,r,t) values( '1 ', 2, 'l ')
insert into t2(s,r,t) values( '1 ', 4, 'h ')
insert into t2(s,r,t) values( '2 ', 2, 'k ')
go

select px = identity(int,1,1) , s , r , t into test from
(
select * , id = 2 from t1
union all
select * , id = 1 from t2
) t
order by s , r , id

delete from t2

insert t2(s,r,t)
select s , r = (select count(1) from test where s = n.s and px < n.px)+1 , t from test n order by s , r

select * from t2

drop table t1,t2 , test

/*
s r t
---------- ----------- ----------
1 1 a
1 2 g
1 3 l
1 4 b
1 5 c
1 6 h
1 7 d
1 8 e