create table #t(a char(1),n int) insert into #t select 'A',3 union all select 'B', 2 select top 1000 ident = identity(int,1,1) into #t1 from syscolumns select a,1 as n,right('000'+cast(ident as varchar(10)),3) resault from #t ,#t1 where n>=identdrop table #t,#t1 a n resault ---- ----------- ------- A 1 001 A 1 002 A 1 003 B 1 001 B 1 002(所影响的行数为 5 行)
create table t(a nvarchar(2), n int) insert t select 'A', 3 union all select 'B', 2set rowcount 10 select ID into # from sysobjects order by ID select t.a, n=1, ID=right('000'+rtrim(b.ID),3) from t join # b on t.n>=b.IDdrop table #,t set rowcount 0a n ID ---- ----------- ------ A 1 001 A 1 002 A 1 003 B 1 001 B 1 002(所影响的行数为 5 行)
insert into #t
select 'A',3 union all select 'B', 2
select top 1000 ident = identity(int,1,1) into #t1 from syscolumns
select a,1 as n,right('000'+cast(ident as varchar(10)),3) resault from #t ,#t1
where n>=identdrop table #t,#t1
a n resault
---- ----------- -------
A 1 001
A 1 002
A 1 003
B 1 001
B 1 002(所影响的行数为 5 行)
insert t select 'A', 3
union all select 'B', 2set rowcount 10
select ID into # from sysobjects order by ID
select
t.a,
n=1,
ID=right('000'+rtrim(b.ID),3)
from
t
join
# b on t.n>=b.IDdrop table #,t
set rowcount 0a n ID
---- ----------- ------
A 1 001
A 1 002
A 1 003
B 1 001
B 1 002(所影响的行数为 5 行)