declare @i int
declare @j int
set @i=6000
set @j=1235
while(@i<7001)
begin
insert aaa select 'PPD'+right(cast(10000000+@j as varchar),7),right(cast(10000000+@i as varchar),7)
set @i=@i+1
set @j=@j+1
end
declare @j int
set @i=6000
set @j=1235
while(@i<7001)
begin
insert aaa select 'PPD'+right(cast(10000000+@j as varchar),7),right(cast(10000000+@i as varchar),7)
set @i=@i+1
set @j=@j+1
end
code varchar(8) --号码
)
declare @codeID int,@code int
select @codeID=1235,@code=6000
while @code<=7000
begin
insert into @t select 'PPD000'+cast(@codeID as varchar),'0000'+cast(@code as varchar)
select @codeID=@codeID+1,@code=@code+1
end select * from @t
--结果
/*
codeID code
---------- --------
PPD0001235 00006000
PPD0001236 00006001
PPD0001237 00006002
PPD0001238 00006003
PPD0001239 00006004
...................
...................
*/
code varchar(8) --号码
)
declare @codeID int,@code int
select @codeID=1235,@code=6000
while @code<=7000
begin
insert into @t select 'PPD000'+cast(@codeID as varchar),'0000'+cast(@code as varchar)
select @codeID=@codeID+1,@code=@code+1
end
codeID varchar(10), --内码
code varchar(8) --号码
)
insert aaa values('PPD0001234','00005213')--现在要插入一批号码 从00006000--00007000 ,要求系统自动生成 内码(内码递增1)
gocreate proc p (@start int,@end int)
as
declare @codeid int
select @codeid=max(cast(right(codeid,7) as int))+1 from aaa
while @start <=@end
begin
insert aaa
select 'PPD'+right(power(10,7)+@codeid,7),right(power(10,8)+@start,8)
set @start=@start+1
set @codeid=@codeid+1
end
go
exec p 6000,7000
select * from aaadrop table aaa
drop proc p
select @a = 1235
select @d = '0000'
select @b = 6000while @b <= 7000
begin
insert aaa values(@c+convert(varchar(4),@a),@d+convert(varchar(4),@b))
@a=@a+1
@b=@b+1
end