如何从 SQL Server 中获取生成的键而不使用 IDENTITY?

huangapple go评论85阅读模式
英文:

How to obtain generated key from sql server without IDENTITY?

问题

我有一个插入行并从中检索生成的键的代码。然而,在 SQL Server 中它无法正常工作。经过我的研究,似乎该列需要具有 IDENTITY 属性。

然而,我已经在 SQL Server 中使用序列来为该列设置属性。它从 1 开始,每次增加 1,最大值是某个特定值。我的查询使用序列来插入行,如下所示:

INSERT INTO event_master (event_id, user_id, type, detail, status, update_timestamp)
VALUES (NEXT VALUE FOR event_master_id_seq, ?,?,?,?,?)

是否有办法使用这种格式来检索生成的值(在我这种情况下是 event_id)?还是说我必须使用 IDENTITY 才能使其工作?

英文:

I have a code that insert a row and retrieve generated key from it. However, it doesn't work with SQL Server. I researched and seems like the column needs IDENTITY property.

However, I am already using Sequence in sql server to set property for that column. It starts at 1, increment by 1 and maximum value is certain value. My query uses the sequence to insert the row like this:

INSERT INTO event_master (event_id, user_id, type, detail, status, update_timestamp)
VALUES (NEXT VALUE FOR event_master_id_seq, ?,?,?,?,?)

Is there any way to use this format to retrieve generated value (in my case, event_id)? or do I have to have IDENTITY in order for it to work?

答案1

得分: 1

我想将这个关闭为 这个问题 的重复,但那个问题没有涉及或询问如何使用序列而不是标识列。另外,我想明确指出这适用于多行(以防您打算使用标量变量)。

DECLARE @event_ids TABLE(event_id int);

INSERT INTO dbo.event_master (event_id, ... other cols)
OUTPUT inserted.event_id INTO @event_ids
VALUES (NEXT VALUE FOR event_master_id_seq, ... other vals);

SELECT event_id FROM @event_ids;

顺便提一句,我真的希望您没有在某个应用程序中使用 ? 占位符,您应该使用显式和强类型的参数。我知道这在 Java 中比更现代的语言更困难,但例如请参阅 这个答案

英文:

I wanted to close this as a duplicate of this one, but that question doesn't address or ask about using sequences instead of identity columns. Also I wanted to make it clear that this works with multiple rows (in case you planned on using a scalar variable).

DECLARE @event_ids TABLE(event_id int);

INSERT INTO dbo.event_master (event_id, ... other cols)
OUTPUT inserted.event_id INTO @event_ids
VALUES (NEXT VALUE FOR event_master_id_seq, ... other vals);

SELECT event_id FROM @event_ids;

As an aside, I really hope you are not using ? placeholders from some application, you should really use explicit and strongly-typed parameters. I know this is harder in Java than more modern languages but see this answer, for example.

huangapple
  • 本文由 发表于 2020年9月24日 23:19:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/64049499.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定