Here's another option...

If I am hearing you correctly, this sort of issue -- replicating and merging databases -- is one reason that MS introduced the newid() function and the uniqueidentifier datatype . Instead of an identity column, you can use GUIDs as PK's. Since GUIDS are unique across time and space (I love saying that) you can import data from multiple locations.

"And now, for the Democratic response...." GUIDS are much slower than identity columns. The more I work with them the less I like 'em. I would *only* recommend even considering them if an app requires importing data from multiple locations.




>Hi Michael,
>
>The SQL database is on a remote server and the offices are located in different locations and they use the same SQL database on the remote server. The shared Office Table has a row for each office with a field for the last transaction Id which will be incremented for each office transactions.
>
>Thanks!!!
>Roland
>===========================================================================
>
>>Can you provide more information regarding your application? Will all offices be using the same database or will each have their own and then replicate around?
>>
>>If everyone is using the same database, I'd use an IDENTITY value and include some kind of SiteID as another column in each row.
>>
>>-Mike
>>
>>>Hi Michael,
>>>
>>>I don't think that would ever happen with these offices since they have only had 27,000 transactions over the last ten years.
>>>
>>>You are right; why put a range limitation.
>>>
>>>But getting back to the question. What would be the proper way and coding to acccomplish this routine?
>>>
>>>Thanks!!!
>>>Roland
>>>
>>>===========================================================================
>>>>>I need to generate and retreive an unique Transaction_id (int field) for different offices; each office would start off with say 1000000001 for Office One and Office Two would start off with 2000000001; etc. The field would be in the Office Table, one row for each office.
>>>>
>>>>And what would you do when an office has used up all of the keys within it's range?
>>>>
>>>>-Mike