January 27, 2012 at 11:16 am
I have a table with a Column "ID" (int) with an Identity Specification; it increments by one.
Is there a way, with a stored procedure, to insert a records and know which record was just inserted, i.e. know what the Column ID value is?
The ID Column is the only Key for the table.
I'd appreciate any help,
January 27, 2012 at 11:24 am
There are a couple of ways to do that. Best (if you're in SQL 2005 or later), is the Output clause:
insert into dbo.MyTable (column list)
output inserted.*
values (value list);
If you're inserting one row at a time, and just want the ID, or are in SQL 2000 or earlier, use Scope_Identity.
insert into dbo.MyTable (column list)
values (value list);
select scope_identity();
Output is better, because it can work with multiple rows, isn't prone to problems caused by triggers, and can put the data into a temp table, table variable, or even a real table, so it can be used later.
Details on how to use the Output clause are here: http://msdn.microsoft.com/en-us/library/ms177564.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2012 at 11:55 am
Thank you,
That worked great. We haven't upgraded yet but are close. I'm I'll make comments in my sp, Thank you again.
January 27, 2012 at 11:57 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply