5

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).

6
  • Are there a finite number of buckets? if so, how many?
    – gbn
    Apr 8 '16 at 8:25
  • Yes, there are. 4096. Buckets are basically "storage bins" for data (and correspond to a table partition each).
    – TomTom
    Apr 8 '16 at 8:31
  • What about one sequence per bucket?
    – gbn
    Apr 8 '16 at 8:38
  • Gets complex. Espcially because I need this about 20 times for different data(so that is a LOT of sequences, also regularly resetting)
    – TomTom
    Apr 8 '16 at 8:39
  • One sequence and reset it then? Or an IDENTITY column and combine with the bucket number to make a computed/composite key column?
    – gbn
    Apr 8 '16 at 8:40
8

Using a single statement and ROW_NUMBER(). With proper indexes, it will probably be more efficient than cursors.

Note that I moved the p.[BucketRef] = @stgBucketNo from the WHERE to the ON clause. The way you had it, made the LEFT JOIN work as an INNER JOIN.

WITH 
  starting (Id) AS
    ( SELECT ISNULL( ( SELECT TOP (1) v.Id 
                       FROM sim.[Variable] AS v 
                       WHERE v.BucketRef = @simBucketNo 
                       ORDER BY v.Id DESC
                     ), 0) 
    ), 
  variables AS
    ( SELECT  
          p.[Variable],
          idRunner = s.Id + ROW_NUMBER() OVER (ORDER BY p.[Variable])
        FROM starting AS s 
          CROSS JOIN simstg.[Parameter] AS p
          LEFT OUTER JOIN sim.[Variable] AS v 
            ON  p.[Variable] = v.[Code] 
            AND p.BucketRef = @stgBucketNo 
        WHERE v.BucketRef = @simBucketNo
          AND v.Code IS NULL
        GROUP BY p.[Variable]
    )
INSERT INTO sim.[Variable] 
    ([BucketRef], [VariableNo], [Code])
SELECT @simBucketNo, v.idRunner, v.[Variable]
FROM variables AS v ;   

The common table subqueries (WITH) are there only for clarity - and to resemble the original code. You can easily rewrite the statement without them:

INSERT INTO sim.[Variable] 
    ([BucketRef], [VariableNo], [Code])
SELECT 
    @simBucketNo,
    idRunner = ISNULL( ( SELECT TOP (1) v.Id 
                         FROM sim.[Variable] AS v 
                         WHERE v.BucketRef = @simBucketNo 
                         ORDER BY v.Id DESC
                       ), 0) 
             + ROW_NUMBER() OVER (ORDER BY p.[Variable])
    p.[Variable],
FROM simstg.[Parameter] AS p
    LEFT OUTER JOIN sim.[Variable] AS v 
        ON  p.[Variable] = v.[Code] 
        AND p.BucketRef = @stgBucketNo 
WHERE v.BucketRef = @simBucketNo
  AND v.Code IS NULL
GROUP BY p.[Variable] ;   
0
4

You can use an identity column in a temporary table to avoid a cursor. This is especially convenient with older SQL Server versions that don't support WITH or window functions.

CREATE TABLE #Variable ([VariableNo] int IDENTITY NOT NULL, [Code] varchar(4000) NOT NULL)
INSERT INTO #Variable ([Code])
    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;

DECLARE @CurID smallint
SELECT @CurID = ISNULL(MAX([Id]),0)
    FROM sim.[Variable]
    WHERE [BucketRef] = @simBucketNo

INSERT INTO sim.[Variable] ([BucketRef], [VariableNo], [Code])
SELECT @simBucketNo, [VariableNo]+@CurID, [Code]
FROM #Variable

DROP TABLE #Variable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.