在执行多个插入时，如何在插入新的sequinentiD值

Question

SQL Server是否保证将以insert语句的订单条款指定的顺序为每行召集NewSequentialId((？

目标是在C＃中列出一个对象列表，每个对象代表要插入表格中的一行，然后很快将它们插入表中。

我要做的是使用sqlbulkcopy将行插入临时表中，然后将行从temp表插入使用newSequentionId((的表中，然后以它们可以成为的方式检索新IDS按与C＃中对象列表相同的顺序排序，以便可以将ID附加到C＃中的每个相应对象。

中

我正在使用SQL Server 2016，这是目标表：

CREATE TABLE dbo.MyTable
(
    Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    SomeNonUniqueValue NVARCHAR(50) NOT NULL
)

首先，我使用sqlbulkcopy将行插入此临时表中。Roworder列包含应用程序中生成的整数。roworder是我需要返回的生成的ID的顺序。在应用程序中，Roworder是列表中每个C＃对象的索引。

CREATE TABLE #MyTableStaging
(
    RowOrder INT NOT NULL,
    SomeNonUniqueValue NVARCHAR(50) NOT NULL
)

然后，我运行此SQL以从#MyTableStaging中取出行，将它们插入mytable并检索插入的ID。

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

在我所有的测试中，这项工作。但是，我最近发现，将行插入到输出条款中指定的表中的顺序并不总是与insert语句中订单指定的顺序相同(我发现这是因为此系统的原始设计是在#MyTableStaging中使用身份，而不是通过＃myTableStaging.id订购。

我知道SQL Server保证身份值是按插入语句的顺序中指定的顺序生成的(来自https://learn.microsoft.com/en-en-en-us/sql/sql/t-sql/statements/insert-transact-sql？view = sql-server-2017＃限制和限制(：

插入使用select with Order by by填充行的查询 保证如何计算身份值，但不能 插入行。

Answer 1

最简单(可能是最有效(的方法是直接插入目标MyTable表中，而无需中间阶段表。我使用表值参数将值表传递到您的存储过程中。

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

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

---

如果您真的想使用登台表，则不能依赖OUTPUT子句返回的行的顺序。您需要在#MyTableStaging.RowOrder和生成的MyTable.Id之间存储显式映射。当您在简单的INSERT语句中使用OUTPUT子句时，您不能将源表中的列包含在输出中。有解决方法。您可以使用MERGE代替INSERT，而MERGE语句的OUTPUT子句允许从源表中进行列。

请参阅非常相似的问题组合输出插入。ID与选定行

的值

MERGE可以INSERT，UPDATE和DELETE行。在我们的情况下，我们只需要INSERT。 1=0始终是错误的，因此NOT MATCHED BY TARGET零件始终执行。通常，可能还有其他分支，请参阅文档。 WHEN MATCHED通常用于UPDATE； WHEN NOT MATCHED BY SOURCE通常用于DELETE，但我们在这里不需要它们。

这种复杂的MERGE形式相当于简单的INSERT，但是与简单的INSERT不同，其OUTPUT子句允许参考我们需要的列。它允许从源表和目标表中检索列，从而保存旧ID和新ID之间的映射。

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，则不必使用它。不过，它的效率将降低。

只需插入所有行。

INSERT INTO dbo.MyTable (SomeNonUniqueValue)
SELECT SomeNonUniqueValue 
FROM #MyTableStaging
;

我们不在乎订单。

如果SomeNonUniqueValue是唯一的，则可以在此列上加入将RowOrder映射到Id。由于这些值不是唯一的，因此我们需要一个额外的步骤，并生成独特的行号以加入。

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是相同的。

示例：

#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               |
+----+--------------------+

您可以这样映射它们：

+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | A  | qwerty             |
|        2 | B  | qwerty             |
|        3 | C  | qwerty             |
|        4 | D  | asdf               |
|        5 | E  | asdf               |
+----------+----+--------------------+

或者，您可以像这样映射它们：

+----------+----+--------------------+
| RowOrder | ID | SomeNonUniqueValue |
+----------+----+--------------------+
|        1 | B  | qwerty             |
|        2 | C  | qwerty             |
|        3 | A  | qwerty             |
|        4 | E  | asdf               |
|        5 | D  | asdf               |
+----------+----+--------------------+

它仍然是一个有效的映射，因为qwerty的所有三个值都是相同的。这些映射都不比另一个映射"更正确"。

显然，如果您的MyTable在INSERT之前没有空，则需要选择新行。