I am trying, failing, to insert rows into a sql table in which one colum is a tracking number consisting of a batch number(xxx) the current date(yyyymmdd) plus the current count of the row being inserted. I am attempting to do this in a single insert/select statement roughly as follows:


set @nCnt=0
set @cBatch='001'
set @cDate=convert(char(8),getdate(),112)

insert into table1 select f1,f2,@cBatch+@cDate+str(++@nCnt,3,0)
from table2


This statement will execute, however the value of ++@nCnt is always zero as orginally set. The desired effect is for @nCnt to be incremented by 1 for each row inserted.

Suggestions please...