about subsuery of tsql (or anything like oracle's sequence in sql server )

exec('insert
into '+@.DestName+ '(AF_nFieldID,AF_nPublicationID,AF_nEditi
onID,AF_nAdTypeID,AF_strCode,AF_strDescription,AF_nSortID,A
F_cIfValid)'+
'select (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+'),SPD_nPubMask,SPD_nEditionMask,SPD_nGrid
ID,SPD_strPageID,SPD_strDescription,SPD_nSortNum,SPD_bIsVal
id from '+@.SourceName )
i want the subsqury (select isnull(max(AF_nFieldID),0)+1
from '+@.DestName+') make a new auto increase ID every
insert,but the sub query only make one ID,or any method
like oracle's sequence?thanksLook at identity in bol.
It will increment the field for every record inserted.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||thank you,i know the identity ,
but my question is that the destinatin table already have
data,and can't change the field to idenity
>--Original Message--
>Look at identity in bol.
>It will increment the field for every record inserted.
>Nigel Rivett
>www.nigelrivett.net
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>