主页 / dba / 问题 / 134741
Accepted
TomTom
Asked: 2016-04-09 00:21:00 +0800 CST

自定义 ID 编号 - 快速插入？

  • 772

作为开始：这是针对序列化的 ETL 存储过程，因此不关心并行性。

我需要在加载期间为标记分配自定义 ID 号。我不能使用身份字段，因为 ID 号是唯一的“桶号”——本质上是另一个编号字段。

我目前使用以下代码：

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

我不喜欢它。它使用我更愿意避免的游标。（旁注：此代码未经测试）

有没有办法在没有光标的情况下更有效地做到这一点？添加新元素时，IR 数必须从使用的最高数开始增加。存在多个存储桶（按存储桶编号）并有自己的编号。

我总是一次只处理一个桶的数据（一个桶在 sim 中，一个在 simstg 中）。

sql-server t-sql

2 个回答

  1. Best Answer

    使用单个语句和ROW_NUMBER()。使用适当的索引，它可能比游标更有效。

    请注意，我将 the p.[BucketRef] = @stgBucketNofrom theWHERE移到了ON子句中。你拥有它的方式，使LEFT JOIN工作成为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 ;

    公用表子查询 ( WITH) 只是为了清晰起见 - 并且类似于原始代码。没有它们，您可以轻松地重写语句：

    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] ;
    • 8

  2. 您可以在临时表中使用标识列来避免游标。这对于不支持WITH或窗口函数的旧 SQL Server 版本特别方便。

    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
    • 4

相关问题