how to calculate and update an 'item number'

[cgv]

Greetings,

I have a pretty large data conversion going on via DTS. Mostly simple migration and reformatting of data.

However, I have a table and I need to generate a serialized number within a given group. Example

Field1 Field2
AAA 1
AAA 2
AAA 3
BBB 1
BBB 2
BBB 3

etc.

I found a way to serialize Field2 across an entire time with a single SQL statement such as:

declare @intCounter int
set @intCounter = 1
update x_GHIS
SET @intCounter = item = @intCounter + 1
where item = 0
go

However, the field 'item' is actually a SMALLINT and cannot be too large - thus the problem. I either need to be able to reset my counter in the SQL statement if it gets above 32767, or find another way.

I can keep track of a global variable within the transformation script I am using - but not sure how I could reset the value of 'item' as Field1 changes. (Well I suppose I could track 2 global variables)

Any elegant ideas? Thanks for any comments!

 

[JayKusch]

Can you set "'item' is actually a SMALLINT " to an Int in the table or are you bound to this size?

Thanks

J. Kusch