|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
get autogenerated id after insertCREATE TABLE dbo.MyTable ( [ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MyTable] PRIMARY KEY, [...] ) ON [PRIMARY] ALTER TABLE dbo.MyTable ADD CONSTRAINT [DF_MyTable_ID] DEFAULT (newid()) FOR [ID] Now I INSERT a new row, and would like to know the ID SQL Server generated for this new row - is there any way to get this generated ID? I know I can create my own ID and supply this to the INSERT statement, but I already had the case that this ID already existed (yeah, I know, highly unlikely, still it happen, and is a real PITA). So I'd like SQL Server itself to create a random ID hoping it will avoid this. thanks, Sam No, there's no counterpart to he SCOPE_IDENTITY for guids.
Declare a variable for the uniqueidentifier datatype, use it in the INSERT and then output that value. This should be no different, logically, from having the NEWID() call in a default constraint for the table, regarding likelihood for collisions. Or, grab the generated value from the table after the INSERT statement, using the natural key. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "Sam Jost" <radeldu***@gmail.com> wrote in message news:1158229816.354195.11380@m73g2000cwd.googlegroups.com... >I got a table created something like this: > > CREATE TABLE dbo.MyTable > ( > [ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MyTable] > PRIMARY KEY, > [...] > ) ON [PRIMARY] > > ALTER TABLE dbo.MyTable ADD CONSTRAINT [DF_MyTable_ID] DEFAULT > (newid()) FOR [ID] > > > Now I INSERT a new row, and would like to know the ID SQL Server > generated for this new row - is there any way to get this generated ID? > > > I know I can create my own ID and supply this to the INSERT statement, > but I already had the case that this ID already existed (yeah, I know, > highly unlikely, still it happen, and is a real PITA). > So I'd like SQL Server itself to create a random ID hoping it will > avoid this. > > thanks, > Sam > Sam
What is the version are you using? Is it crtitical to use ROWGUIDCOL instead of an IDENTITY() property or generating your own ID ? As you probably aware this value (NEWID()) is diffrerent for each computer according to BOL Show quote "Sam Jost" <radeldu***@gmail.com> wrote in message news:1158229816.354195.11380@m73g2000cwd.googlegroups.com... >I got a table created something like this: > > CREATE TABLE dbo.MyTable > ( > [ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MyTable] > PRIMARY KEY, > [...] > ) ON [PRIMARY] > > ALTER TABLE dbo.MyTable ADD CONSTRAINT [DF_MyTable_ID] DEFAULT > (newid()) FOR [ID] > > > Now I INSERT a new row, and would like to know the ID SQL Server > generated for this new row - is there any way to get this generated ID? > > > I know I can create my own ID and supply this to the INSERT statement, > but I already had the case that this ID already existed (yeah, I know, > highly unlikely, still it happen, and is a real PITA). > So I'd like SQL Server itself to create a random ID hoping it will > avoid this. > > thanks, > Sam > Hi,
After your insert statement, just run select @@IDENTITY or select SCOPE_IDENTITY() Regards, Karthik Show quote "Sam Jost" wrote: > I got a table created something like this: > > CREATE TABLE dbo.MyTable > ( > [ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MyTable] > PRIMARY KEY, > [...] > ) ON [PRIMARY] > > ALTER TABLE dbo.MyTable ADD CONSTRAINT [DF_MyTable_ID] DEFAULT > (newid()) FOR [ID] > > > Now I INSERT a new row, and would like to know the ID SQL Server > generated for this new row - is there any way to get this generated ID? > > > I know I can create my own ID and supply this to the INSERT statement, > but I already had the case that this ID already existed (yeah, I know, > highly unlikely, still it happen, and is a real PITA). > So I'd like SQL Server itself to create a random ID hoping it will > avoid this. > > thanks, > Sam > > AFAIK the server doesn't verify whether a value already exists or not.
Your best choice is to use the OUTPUT clause (on SQL 2005) or simply use the natural key to access the rows - you're considering multiple inserts, aren't you? ML --- http://milambda.blogspot.com/ > Your best choice is to use the OUTPUT clause Hmm, why didn't I think of that?Show quote :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:6EF54B2D-76B2-4694-807E-12BD815BD53B@microsoft.com... > AFAIK the server doesn't verify whether a value already exists or not. > > Your best choice is to use the OUTPUT clause (on SQL 2005) or simply use the > natural key to access the rows - you're considering multiple inserts, aren't > you? > > > ML > > --- > http://milambda.blogspot.com/ Ah, come on, you know you did, but the keyboard failed to notice it. Happens
to me now and then, too. ;) ML --- http://milambda.blogspot.com/ :-)
--
Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Show quote
"ML" <M*@discussions.microsoft.com> wrote in message
news:9856201F-29A8-4963-A01C-030603307BA6@microsoft.com... > Ah, come on, you know you did, but the keyboard failed to notice it. Happens > to me now and then, too. ;) > > > ML > > --- > http://milambda.blogspot.com/ Thanks, the output clause does work nicely.
Just to document what I do, for single inserts I now use: INSERT INTO dbo.MyTable ([MyField]) OUTPUT inserted.ID VALUES (@mydata) This way INSERT returns the Guid as scalar result. I found an example for multi-column as well: http://www.sqlteam.com/item.asp?ItemID=25496 thanks, Sam -- PS: of course it would be a pity if SQL Server 2005 does not use this information to prevent non-unique RowGuids - but at least not it got a chance ;) ML schrieb: Show quote > AFAIK the server doesn't verify whether a value already exists or not. > > Your best choice is to use the OUTPUT clause (on SQL 2005) or simply use the > natural key to access the rows - you're considering multiple inserts, aren't > you? > > > ML > > --- > http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics