在INSERT语句上返回主键

本文介绍了在INSERT语句上返回主键的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在将一条记录插入一个自动生成 唯一ID（即主键）的表中。无论如何都要返回此ID。因为我在ASP页面上使用这个我真的需要ID来更新 页面的新细节。 我觉得在mysql上有一个名为LAST_INSERT_ID的东西，它确实是这个。

I am inserting a record into a table that automatically generates unique ids (i.e. Primary Key). Is there anyway to return this id. As I am using this on ASP page and I really need the ID to update the page with the new details. I think on mysql there is something called LAST_INSERT_ID which does this.

推荐答案

所以这是一个IDENTITY柱？在这种情况下，您可以在SQL 2000中使用SCOPE_IDENTITY（） 来返回在当前 连接上生成的最后一个标识值。使用SQL 7及更早版本，您可以使用@@ IDENTITY，但值 也将反映由触发器插入产生的标识值。 - 希望这会有所帮助。 Dan Guzman SQL Server MVP " ree32" ; <再*** @ hotmail>在消息中写道 news：76 ************************** @ posting.google.c om ... So this is an IDENTITY column? In this case, you can use SCOPE_IDENTITY() in SQL 2000 to return the last identity value generated on the current connection. With SQL 7 and earlier, you can use @@IDENTITY but the value will reflect identity values resulting from trigger inserts as well. -- Hope this helps. Dan Guzman SQL Server MVP "ree32" <re***@hotmail> wrote in message news:76**************************@posting.google.c om... 我正在将一条记录插入到一​​个自动生成唯一ID（即主键）的表中。无论如何都要返回此ID。因为我在ASP页面上使用它，我真的需要ID来更新页面的新细节。 我认为在mysql上有一些叫做LAST_INSERT_ID的东西这个。 I am inserting a record into a table that automatically generates unique ids (i.e. Primary Key). Is there anyway to return this id. As I am using this on ASP page and I really need the ID to update the page with the new details. I think on mysql there is something called LAST_INSERT_ID which does this.

你能举例说明一组值和列名，因为我是 不确定如何使用此声明。 Dan Guzman <顾****** @ nospam-online.sbcglobal>在消息新闻中写道：< tD ***************** @ newssvr30.news.prodigy.c om> ... Can you give an example with a set of values and column names as I am not sure how to use this statement. "Dan Guzman" <gu******@nospam-online.sbcglobal> wrote in message news:<tD*****************@newssvr30.news.prodigy.c om>... 所以这是一个IDENTITY列？在这种情况下，您可以在SQL 2000中使用SCOPE_IDENTITY（）返回在当前连接上生成的最后一个标识值。使用SQL 7及更早版本，您可以使用@@ IDENTITY，但值也会反映触发器插入所产生的身份值。 - 希望这会有所帮助。 /> Dan Guzman SQL Server MVP So this is an IDENTITY column? In this case, you can use SCOPE_IDENTITY() in SQL 2000 to return the last identity value generated on the current connection. With SQL 7 and earlier, you can use @@IDENTITY but the value will reflect identity values resulting from trigger inserts as well. -- Hope this helps. Dan Guzman SQL Server MVP

这是一个使用proc生成的行插入行的示例价值 作为单行，单列结果返回 CREATE TABLE MyTable （ MyPK int NOT NULL IDENTITY（1,1） CONSTRAINT PK_MyTable PRIMARY KEY， MyData int NOT NULL ） GO CREATE PROC MyProc @MyData int AS SET NOCOUNT ON INSERT INTO MyTable（MyData）VALUES（1） SELECT SCOPE_IDENTITY（） GO EXEC MyProc @MyData = 1 GO - 希望这会有所帮助。 Dan Guzman SQL Server MVP " ree32" <再*** @ hotmail>在消息中写道 news：76 ************************** @ posting.google.c om ... Here''s an example that inserts a row using a proc with the generated value returned as a single-row, single-column result CREATE TABLE MyTable ( MyPK int NOT NULL IDENTITY(1,1) CONSTRAINT PK_MyTable PRIMARY KEY, MyData int NOT NULL ) GO CREATE PROC MyProc @MyData int AS SET NOCOUNT ON INSERT INTO MyTable (MyData) VALUES(1) SELECT SCOPE_IDENTITY() GO EXEC MyProc @MyData = 1 GO -- Hope this helps. Dan Guzman SQL Server MVP "ree32" <re***@hotmail> wrote in message news:76**************************@posting.google.c om... 你能举例说明一组值和列名，因为我不知道如何使用这个陈述。 Dan Guzman <顾****** @ nospam-online.sbcglobal>在消息中写道新闻：< tD ***************** @ newssvr30.news.prodigy.c om> ... Can you give an example with a set of values and column names as I am not sure how to use this statement. "Dan Guzman" <gu******@nospam-online.sbcglobal> wrote in message news:<tD*****************@newssvr30.news.prodigy.c om>... 那么这是一个IDENTITY专栏？在这种情况下，您可以在SQL 2000中使用 SCOPE_IDENTITY（）返回在当前连接上生成的最后一个标识值。使用SQL 7及更早版本，您可以使用@@ IDENTITY，但值也会反映触发器插入所产生的身份值。 - 希望这会有所帮助。 /> Dan Guzman SQL Server MVP So this is an IDENTITY column? In this case, you can use SCOPE_IDENTITY() in SQL 2000 to return the last identity value generated on the current connection. With SQL 7 and earlier, you can use @@IDENTITY but the value will reflect identity values resulting from trigger inserts as well. -- Hope this helps. Dan Guzman SQL Server MVP