Identity on Insert 1

[CDNNewbie]

Be forewarend
I'm an accidental DBA, so I am in the process of learning.

I am trying to insert a set of records into an existing table. This table has two fields which make up the primary key (strOrderNumber and lngSort). For specific reasons related to our accounting software, I have NOT setup lngSort to be an identity column, however each record needs to be distinct. Currently we input each record one row at a time so I am able to iterate the lngSort. However now I am trying to do a mass insert for a specific strOrderNumber, and I need to somehow iterate the lngSort.

I have tried (SELECT MAX(lngSort) + 1 FROM **tablename** WHERE strOrderNumber = **strOrderNumber) AS lngSort, as part of the Insert query, but that has not worked. Can I somehow use IDENTITY to make this work? If so how?

Thank-you in advance!

 

[RiverGuy]

If you're doing an "INSERT INTO .... SELECT * FROM," then use the ROW_NUMBER function to get an incrementing value.

 

[CDNNewbie]

Thanks, it worked!