As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern.
I need to assign custom ID numbers for markers during loads. I can not use an identity field, because the ID numbers are to be unique by a "bucket number" - essentially another numbered field.
I currently use the following code:
DECLARE @idRunner smallint SELECT @idRunner = ISNULL(MAX([Id]),0) FROM sim.[Variable] WHERE [BucketRef] = @simBucketNo DECLARE variable_cursor CURSOR FOR SELECT DISTINCT p.[Variable] FROM simstg.[Parameter] p LEFT OUTER JOIN sim.[Variable] v ON (p.[Variable] = v.[Code]) WHERE p.[BucketRef] = @stgBucketNo AND v.BucketRef = @simBucketNo AND v.Code IS NULL OPEN variable_cursor DECLARE @variable VARCHAR(64) FETCH NEXT FROM variable_cursor INTO @variable WHILE @@FETCH_STATUS = 0 BEGIN SET @idRunner = @idRunner + 1 INSERT INTO sim.[Variable] ([BucketRef], [VariableNo], [Code]) VALUES (@simBucketNo, @idRunner, @variable) FETCH NEXT FROM variable_cursor INTO @variable END CLOSE variable_cursor DEALLOCATE variable_cursor
and I Do not like it. It uses cursors which I prefer to avoid. (Sidenote: this code is untested)
Is there a way to do this more efficient, without a cursor? IR numbers must increase from the highest used when new elements are added. Multiple buckets exist (by bucket no) and have their own numbering.
I am always only going to process data for one bucket at a time (one bucket in sim, one in simstg).