Return value after INSERT

I'm trying to get a the key-value back after an INSERT-statement. Example: I've got a table with the attributes name and id. id is a generated value.

    INSERT INTO table (name) VALUES('bob');

Now I want to get the id back in the same step. How is this done?

We're using Microsoft SQL Server 2008.

---

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

---

Use SCOPE_IDENTITY() to get the new ID value

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()

http://msdn.microsoft.com/en-us/library/ms190315.aspx

---

INSERT INTO files (title) VALUES ('whatever'); 
SELECT * FROM files WHERE id = SCOPE_IDENTITY();

Is the safest bet since there is a known issue with OUTPUT Clause conflict on tables with triggers. Makes this quite unreliable as even if your table doesn't currently have any triggers - someone adding one down the line will break your application. Time Bomb sort of behaviour.

See msdn article for deeper explanation:

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults.aspx

链接地址: http://www.djcxy.com/p/94516.html

上一篇: 返回标识作为输出参数或标量

下一篇: INSERT后的返回值