Custom ID Numbering - fast way on insert?
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] ;