首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何在执行多个插入时获得插入的NewSequentialId值?

问如何在执行多个插入时获得插入的NewSequentialId值?
EN

Stack Overflow用户
提问于 2019-05-16 02:50:41
回答 1查看 934关注 0票数 2

Server是否保证按INSERT语句的order子句指定的顺序调用每一行的NewSequentialId()?

目标是在C#中获取一个对象列表,每个对象表示要插入到表中的一行,并很快将它们插入到表中。

我要做的是使用SqlBulkCopy将行插入临时表,然后将临时表中的行插入使用NewSequentialId()的表中,然后以与C#中的对象列表相同的顺序检索新in,以便将in附加到C#中的每个对应对象。

我使用的是Server 2016,这是目标表:

代码语言:javascript
复制
CREATE TABLE dbo.MyTable
(
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    SomeNonUniqueValue NVARCHAR(50) NOT NULL
)

首先,我使用SqlBulkCopy将行插入到这个临时表中。RowOrder列包含在应用程序中生成的整数。RowOrder是我需要返回生成的in的顺序。在应用程序中,RowOrder是列表中每个C#对象的索引。

代码语言:javascript
复制
CREATE TABLE #MyTableStaging
(
    RowOrder INT NOT NULL,
    SomeNonUniqueValue NVARCHAR(50) NOT NULL
)

然后我运行这个SQL来获取#MyTableStaging中的行,将它们插入到MyTable中并检索插入的ID。

代码语言:javascript
复制
DECLARE @MyTableOutput TABLE
(
    Id UNIQUEIDENTIFIER NOT NULL
)

INSERT INTO dbo.MyTable (SomeNonUniqueValue)
OUTPUT Inserted.Id INTO @MyTableOutput(Id)
SELECT SomeNonUniqueValue 
FROM #MyTableStaging
ORDER BY RowOrder

SELECT Id FROM @MyTableOutput ORDER BY Id

在我所有的测试中,这是可行的。但是,我最近发现,在OUTPUT子句中指定的表中插入行的顺序并不总是与INSERT语句中order指定的顺序相同(我发现这是因为这个系统的最初设计是在#MyTableStaging中使用标识,而不是由#MyTableStaging.Id排序,而是按identity列排序)。

我知道Server保证按照INSERT语句的order子句(来自https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017#limitations-and-restrictions)中指定的顺序生成标识值:

使用SELECT with BY填充行的INSERT查询保证计算标识值的方式,但不保证插入行的顺序。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-05-16 08:35:43

最简单(也可能是最有效)的方法是直接插入目标MyTable表,而不使用中间暂存表。我会使用表值参数将值表传递到存储过程中。

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

如果确实希望使用暂存表,则不能依赖OUTPUT子句返回的行顺序。您需要存储#MyTableStaging.RowOrder和生成的MyTable.Id之间的显式映射。在简单的OUTPUT语句中使用INSERT子句时,不能将源表中的列包含到输出中。有个解决办法。您可以使用MERGE而不是MERGE语句的INSERT和OUTPUT子句来允许从源表中的列。

见非常类似的问题Combine OUTPUT inserted.id with value from selected row

MERGE可以INSERT、UPDATE和DELETE行。在我们的例子中,我们只需要INSERT。1=0总是假的,所以NOT MATCHED BY TARGET部件总是被执行。通常,还可以有其他分支,请参阅docs。WHEN MATCHED通常习惯于UPDATE;WHEN NOT MATCHED BY SOURCE通常习惯于DELETE,但我们这里不需要它们。

这种复杂形式的MERGE等价于简单INSERT,但与简单INSERT不同,它的OUTPUT子句允许引用我们需要的列。它允许从源表和目标表中检索列,从而保存旧ID和新ID之间的映射。

代码语言:javascript
复制
DECLARE @MyTableOutput TABLE
(
    OldRowOrder int NOT NULL
    ,NewID UNIQUEIDENTIFIER NOT NULL
);


MERGE INTO dbo.MyTable
USING
(
    SELECT RowOrder, SomeNonUniqueValue
    FROM #MyTableStaging
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (SomeNonUniqueValue)
VALUES (Src.SomeNonUniqueValue)
OUTPUT Src.RowOrder AS OldRowOrder, inserted.ID AS NewID
INTO @MyTableOutput(OldRowOrder, NewID)
;

如果您的DBA如此害怕MERGE,则不必使用它。不过,这样做的效率会更低。

只需插入所有行即可。

代码语言:javascript
复制
INSERT INTO dbo.MyTable (SomeNonUniqueValue)
SELECT SomeNonUniqueValue 
FROM #MyTableStaging
;

我们不关心命令。

如果SomeNonUniqueValue是唯一的,您可以在本专栏中加入,将RowOrder映射到Id。由于这些值并不是唯一的,因此我们需要额外的一步并为连接生成唯一的行号。

代码语言:javascript
复制
WITH
CTE_Dst
AS
(

    SELECT
        Id
        ,SomeNonUniqueValue
        ,ROW_NUMBER() OVER (ORDER BY SomeNonUniqueValue) AS rn
    FROM dbo.MyTable
)
,CTE_Src
AS
(

    SELECT
        RowOrder
        ,SomeNonUniqueValue
        ,ROW_NUMBER() OVER (ORDER BY SomeNonUniqueValue) AS rn
    FROM #MyTableStaging
)
SELECT
    CTE_Dst.Id
    ,CTE_Src.RowOrder
FROM
    CTE_Dst
    INNER JOIN CTE_Src ON CTE_Src.rn = CTE_Dst.rn
;

如果您有三行具有相同SomeNonUniqueValue的行,那么如何将这些行映射到一起并不重要,因为SomeNonUniqueValue是相同的。

示例:

代码语言:javascript
复制
#MyTableStaging
+----------+--------------------+
| RowOrder | SomeNonUniqueValue |
+----------+--------------------+
|        1 | qwerty             |
|        2 | qwerty             |
|        3 | qwerty             |
|        4 | asdf               |
|        5 | asdf               |
+----------+--------------------+

MyTable
+----+--------------------+
| ID | SomeNonUniqueValue |
+----+--------------------+
| A  | qwerty             |
| B  | qwerty             |
| C  | qwerty             |
| D  | asdf               |
| E  | asdf               |
+----+--------------------+

你可以像这样绘制地图:

代码语言:javascript
复制
+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | A  | qwerty             |
|        2 | B  | qwerty             |
|        3 | C  | qwerty             |
|        4 | D  | asdf               |
|        5 | E  | asdf               |
+----------+----+--------------------+

或者,你可以像这样绘制地图:

代码语言:javascript
复制
+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | B  | qwerty             |
|        2 | C  | qwerty             |
|        3 | A  | qwerty             |
|        4 | E  | asdf               |
|        5 | D  | asdf               |
+----------+----+--------------------+

它仍然是一个有效的映射,因为qwerty的所有三个值都是相同的。这两个映射都没有一个比另一个“更正确”。

显然,如果MyTable在INSERT之前不是空的,则只需要选择新行。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/56160120

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档