If you need to create a derived key such as this, then a common approach would be to have a second table. This table would consist of the year number and a count of the number of records already inserted for that year. So, it would look something like this:
CREATE TABLE [dbo].[YEAR_MANAGER]
(
YEAR_PART int NOT NULL,
COUNT_YEAR int NOT NULL
)
Now, this is where things get slightly complicated so please bear with me on this. First of all, you need to create a transaction to run the SQL in. We are going to rely on this to help us lock the year row while we are getting the value. If you are doing this dynamically (in other words, you are adding the years in only when you get files in that match, i.e. you aren't prepopulating the years), you need to do the following
INSERT INTO [dbo].[YEAR_MANAGER]
SELECT @Year, 0
FROM [dbo].[YEAR_MANAGER]
WHERE NOT EXISTS (SELECT COUNT(1) FROM [dbo].[YEAR_MANAGER] WHERE YEAR_PART = @Year)
This gives you a populated row for the year. Now, comes the clever part (but it's a bit complex, so please follow closely). At this part, we are going to update the row and then return it like this:
UPDATE [dbo].[YEAR_MANAGER]
SET COUNT_YEAR = COUNT_YEAR + 1
WHERE YEAR_PART = @YEAR;
SELECT YEAR_PART FROM [dbo].[YEAR_MANAGER] WHERE YEAR_PART = @YEAR
Effectively, what this snippet does is updates the count of the rows for that particular year. As we are inside a transaction, we are going to lock this row until we have done the row and returned it. Make sure that you have chosen an appropriate locking method for your update such as an exclusive lock. The reason we do the update first is to get the lock, once we have updated the row, we can select the value from it and use this; in other words, we get the value because we have successfully taken the lock out. From this, you can see that the reason we initialise the count to 0 is because we do the update before we select it.
I hope that his helps. I apologise in advance if the syntax isn't 100% exact here, as I've just knocked this example up in the editor here on CP.