英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论