January 12, 2003 at 6:13 am
The best way is use a stored proc, something like this:
create proc usp_InsertData @Somedata varchar(20), @ID int output as
set nocount on
insert into dbo.SomeTable (DataCol) values (@SomeData)
select @ID = Scope_Identity (or select @ID=@@Identity in SQL7)
The other way is to provide some info that is guaranteed unique, whether it be the actual data or something else, like a guid that you generate on the client. Then you can do the insert, then so a select on the unique data to get back the ident value.
Using the proc is better, only one round trip, no hassles with extra unique values. In this case I'd look at trying to pass all the info to one proc, have it do the other inserts as well, you probably dont need the value on the client. If you do need the key(s) on the client, consider using uniqueidentifier instead, you can generate the keys on the client and not have to retrieve them.
Andy
January 12, 2003 at 1:42 pm
Andy,
Thank you very much for pointing out the @@IDENTITY. It certainly works very well. However, that's when I take off the triggers on the table.
When the triggers are in place and they create entries in other tables, the last entered identity is returned.
I have got around it by extending the trigger to put data into another extra table with the same identity characteristics as the table in question. This works fine but looks a bit messy. Can you think of another way around the trigger problem?
Thanks again.
Calvin
January 12, 2003 at 1:47 pm
Scope_identity was created to fix that, the behavior of @@identity you're seeing is correct. Relying on the idents to stay the same is doomed, I'd look for another way!
Other than the way I mentioned earlier, you could work around it by tagging the original record as pending (or inserting the pkey into a work table without an ident col), then have a job do the insert into the other table that does have an ident and update the status/delete the row. Run the job every min wouldnt cost much and latency would be minimal.
Andy
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply