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!
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!