Try something like

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

INSERT INTO table 1
SELECT f1,f2,@cBatch+cDate+str(@nCnt,3,0),@nCnt = @nCnt + 1

Let me know if it works, I need to do something similar to this soon.

BTW: There is no ++ operator in t-sql.

BOb


>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...