sql - 进行多次插入时如何获取插入的 NewSequentialId 值?
问题描述
SQL Server 是否保证将按照 INSERT 语句的 ORDER BY 子句指定的顺序为每一行调用 NewSequentialId()?
目标是在 C# 中获取一个对象列表,每个对象代表要插入到表中的一行,然后相当快地将它们插入到表中。
我要做的是使用 SqlBulkCopy 将行插入到临时表中,然后将临时表中的行插入到使用 NewSequentialId() 的表中,然后以可以在与 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
在我所有的测试中,这都有效。但是我最近发现,OUTPUT子句中指定的表中插入行的顺序并不总是和INSERT语句中ORDER BY指定的顺序相同(我发现这个是因为这个系统的原始设计是在#MyTableStaging 中使用标识,而不是按#MyTableStaging.Id 排序,而是按标识列排序)。
我知道 SQL Server 保证按照 INSERT 语句的 ORDER BY 子句中指定的顺序生成标识值(来自https://docs.microsoft.com/en-us/sql/t-sql/statements/insert- transact-sql?view=sql-server-2017#limitations-and-restrictions):
使用带有 ORDER BY 的 SELECT 来填充行的 INSERT 查询保证了如何计算标识值,但不保证插入行的顺序。
解决方案
最简单(也可能是最有效)的方法是直接插入目标MyTable
表而无需中间临时表。我会使用表值参数将值表传递到您的存储过程中。
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
如果你真的想使用临时表,你不能依赖OUTPUT
子句返回的行的顺序。您需要存储#MyTableStaging.RowOrder
和 生成的MyTable.Id
. OUTPUT
在简单语句中使用子句时,INSERT
不能将源表中的列包含到输出中。有一种解决方法。您可以使用允许来自源表的列的语句MERGE
代替INSERT
andOUTPUT
子句。MERGE
看到非常相似的问题将 OUTPUT inserted.id 与所选行中的值结合起来
MERGE
可以INSERT
和UPDATE
行DELETE
。在我们的例子中,我们只需要INSERT
.
1=0
总是假的,所以该NOT MATCHED BY TARGET
部分总是被执行。一般来说,可能还有其他分支,请参阅文档。
WHEN MATCHED
通常用于UPDATE
;
WHEN NOT MATCHED BY SOURCE
通常用于DELETE
,但我们在这里不需要它们。
这种复杂的形式MERGE
等同于 simple INSERT
,但与 simple 不同的是,INSERT
它的OUTPUT
子句允许引用我们需要的列。它允许从源表和目标表中检索列,从而保存新旧 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
,则只需选择新行。
推荐阅读
- sql - 添加多个 LIKE 参数不会产生任何结果
- cypress - 我如何在柏树中单击鼠标
- radio-button - 无法根据单选按钮输入切换选择屏幕
- php - 如何在 url codeigniter 中显示检查的过滤器变量值
- scala - 如何从 CSV 文件转换为 scala toList?
- python - 如何使用另一个列表从列表中选择元素?
- android - 单击微调器选项时在地图上放置一个标记 Android Studio
- azure - 保护 Azure 中的微服务使用的资源连接数据/字符串
- angular - 在 Angular、Material 和 Flex-Layout 上以 mat-progress-spinner 为中心的叠加文本
- javascript - 量角器黄瓜框架未识别规范文件