SQL Server：为新添加的行选择主键编号

发布于 2024-11-30 18:50:41 字数 449 浏览 2 评论 0原文

我当前正在

SELECT MAX(CustomerID)...

选择 CustomerID，它是表的主键，新添加的行的编号，因为我需要将此编号用于另一个使用它作为外键的表。通过 INSERT 添加一行，并按顺序执行一个 SELECT 查询。

在 SQL Server Management Studio 的列属性下，我看到 Identity Increment 设置为 1。使用 MAX(CustomerID) 获取我需要的内容是否存在不安全的原因？对于此过程，代码将始终是“一行 INSERT 和一个 SELECT 查询”。如果有更好的方法，那会是什么？也许，在某个地方，“给我新添加的行的 CustomerID”？

原文

I'm currently using

SELECT MAX(CustomerID)...

to select the CustomerID, which is the primary key of the table, number of a newly added row because I need to use this number for another table that uses it as a foreign key. One row is added via INSERT, and one SELECT query is performed sequentially.

Under column property in SQL Server Management Studio, I see the Identity Increment is set to 1. Is there an unsafe reason to use MAX(CustomerID) to get what I need? The code will always be "one row INSERT and one SELECT queries" for this procedure. If there is a better way, what would that be? Perhaps, somewhere along the line, "get me the CustomerID of the newly added row"?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

她如夕阳 2024-12-07 18:50:41

在并发条件下（并且当表为空时）

使用 SELECT SCOPE_IDENTITY() 或 OUTPUT，您建议的 MAX(CustomerID) 方法将失败> 条款。以下两者的示例

CREATE TABLE #Customers
(
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50)
)

INSERT INTO #Customers
(Name) 
VALUES ('Customer 1')

SELECT SCOPE_IDENTITY()

INSERT INTO #Customers
(Name) 
OUTPUT inserted.CustomerID
VALUES ('Customer 1')


DROP TABLE #Customers

Your suggested approach with MAX(CustomerID) will fail under conditions of concurrency (and when the table is empty)

use SELECT SCOPE_IDENTITY() or the OUTPUT clause. Examples of both below

CREATE TABLE #Customers
(
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50)
)

INSERT INTO #Customers
(Name) 
VALUES ('Customer 1')

SELECT SCOPE_IDENTITY()

INSERT INTO #Customers
(Name) 
OUTPUT inserted.CustomerID
VALUES ('Customer 1')


DROP TABLE #Customers

回复收藏 0 原文

~没有更多了~