September 30, 2009 at 12:12 pm
I have a table where uniqueidentifer is the primary key and is
generated with newid() in default value.
How do I get the last value generated.
Rajesh Halyal
September 30, 2009 at 12:18 pm
It would be trivially easy to do that in SQL 2005, with the Output clause that added.
For SQL 2000 (which is the forum you posted in, so I assume it's what you're looking for), I don't believe there's a function for that. HOWEVER, you could achieve that functionality in an insert proc with an output parameter, or for internal use in the proc.
Here's a sample of how it could be done.
declare @ID uniqueidentifier;
select @ID = newid();
insert into dbo.MyTable (ID, Col1)
select @ID, Col1Value;
Change the "declare" statement to an output parameter at the top of the proc definition, and you could return the value to a calling procedure or the application.
- 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
September 30, 2009 at 12:52 pm
GSquared (9/30/2009)
It would be trivially easy to do that in SQL 2005, with the Output clause that added.For SQL 2000 (which is the forum you posted in, so I assume it's what you're looking for), I don't believe there's a function for that. HOWEVER, you could achieve that functionality in an insert proc with an output parameter, or for internal use in the proc.
Here's a sample of how it could be done.
declare @ID uniqueidentifier;select @ID = newid();
insert into dbo.MyTable (ID, Col1)
select @ID, Col1Value;
Change the "declare" statement to an output parameter at the top of the proc definition, and you could return the value to a calling procedure or the application.
Side Question: Do you know of any way to do this in a trigger without a cursor in 2000?
September 30, 2009 at 12:53 pm
Garadin (9/30/2009)
GSquared (9/30/2009)
It would be trivially easy to do that in SQL 2005, with the Output clause that added.For SQL 2000 (which is the forum you posted in, so I assume it's what you're looking for), I don't believe there's a function for that. HOWEVER, you could achieve that functionality in an insert proc with an output parameter, or for internal use in the proc.
Here's a sample of how it could be done.
declare @ID uniqueidentifier;select @ID = newid();
insert into dbo.MyTable (ID, Col1)
select @ID, Col1Value;
Change the "declare" statement to an output parameter at the top of the proc definition, and you could return the value to a calling procedure or the application.
Side Question: Do you know of any way to do this in a trigger without a cursor in 2000?
Depends on what you're trying to do and why you're doing it in a trigger.
- 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
September 30, 2009 at 1:48 pm
[Edit] Nevermind, apparently I never actually stopped to think about this one, it wasn't really a difficult problem.
September 30, 2009 at 3:11 pm
Stopping to think about this for a few, I realized I could just select each UID (Yeah, the table has a UID already, and it's NOT the field used to link it to other tables.) (...seriously) into a temp table and use another newid() call to populate a value for the current serial number field for each one. Then I could use this to insert into all the other tables.
October 1, 2009 at 7:44 am
If you're inserting from a dataset, you can also join to a temp table that has a column with a newid default constraint, and get all of them at the same time. No reason to do it one row at a time.
- 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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply