laurie-789651 (8/28/2012)

---

This will work for adding one at a time:

--------------------------------------------------------------

drop table #test;

create table #test ( id varchar(13) );

--------------------------------------------------------------

declare @id varchar(13), @nextid varchar(13);

select @id = max(id) from #test;

select @nextid = case when left(@id, 8) = convert(varchar(8), getdate(), 112)

then convert(varchar(8), getdate(), 112) +

right('0000' + cast(cast(substring(@id, 9, 5) as int) +1 as varchar(5)),5)

else convert(varchar(8), getdate(), 112) + '00001'

end;

insert into #test values ( @nextid );

--------------------------------------------------------------

select * from #test;

--------------------------------------------------------------