To expand on Brandie's option, this solution is kind of ugly and you can only insert 1 Emp row at a time. You would also have to do all inserts with some extra code or in a stored procedure (the preferred way). The table would look like this:

CREATE TABLE NextEmpTbl

( NextJapanID INT

, NextChinaID INT

)

INSERT INTO NextEmpTbl

( NextJapanID, NextChinaID )

SELECT 1000, 50000

This gives 40,000 ID's that can be used for Japan. If you need more then the gap between them should be much larger.

The stored procedure would have logic in it like this:

CREATE PROCEDURE InsertEmp

@CountryCode VARCHAR(5) -- Pass In either 'JAPA' or 'CHIN'

DECLARE

@NextID INT

, @NewEmp VARCHAR(10)

-- This type of update ensures no-one else can get the number

-- you are getting.

IF @CountryCode = 'JAPA'

UPDATE NextEmpTbl SET @NextID = NextJapanID

, NextJapanID = NextJapanID + 1

ELSE

UPDATE NextEmpTbl SET @NextID = NextChinaID

, NextChinaID = NextChinaID + 1

SET @NewEmp = @CountryCode

+ RIGHT('000000' + CONVERT(VARCHAR, @NewID))

-- You can now use @NewEmp for the ID.

-- It's ugly and you can only insert one at a time this way.

Todd Fifield