Query insert a record in sql server table like 2013040900001 -

how insert record in sql server 2005 table formatted date , appended auto incremented numeric value?

for example if today's date 2013-04-09 12:05:44.640 able format 20130409 using convert(varchar, getdate(), 112).

but want insert record 2013040900001 next 2013040900002, 2013040900003 , on.

a solution generating system-wide auto-incrementing numbers via table designed task:

create table [autoinc] (    [number] int identity (1,1) not null,    [col1] char(1) not null ); 

then insert value table , remove rollback:

begin tran; insert autoinc values ('a'); rollback tran; 

this ensures no space used (the table empty) create number (because values generated identity attribute not participate in transaction). can obtain incremented value using scope_identity() function , append date:

insert yourtable (yourcol) values (convert(varchar, getdate(), 112) + right('000000000' + cast(scope_identity() varchar(10)), 10));