| Author |
Topic |
|
gshacte
Starting Member
17 Posts |
 Posted - 2010-02-05 : 17:42:35
|
| We have a table whose records we want to essentially duplicate in a second table, but we want that second table to include the unique record id that sql writes to rows of the first table. We don't want to read the record id with the rec id function that does so, since we could not be certain that another record was added after the first assert, such that the record id we retrieved this way from the first table, might apply to a record subsequently added to the table. Is there a way to capture the automatic rec id that is written to a row and stored in a variable such that we can then be sure it is the rec id that was used to store that row. I suppose we can just reread the row we wrote via a key field we can define, and then create a row in the second table with the record id from the first, but that seems a bit clumsy to always have to reread a row just written.Hope I didn't make this too muddy. Thanks much for any attention to this.Gerry |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-05 : 17:57:07
|
| Are you talking about an IDENTITY column? Is so, then I'd think you want the SCOPE_IDENTITY() function. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-06 : 05:56:47
|
| i think what you need is to do both the inserts inside same procedure each time capturing the id value generated using SCOPE_IDENTITY as suggested by Lamprey and then use it in second insert. One question here would be whether you'll have batch inserts? |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-06 : 21:04:59
|
| Thank you much. I am concerned that after asserting a row into the first table but before the procedure executes the scope_identity function to retrieve the identity value, that some other user might get to the point in the procedure where the assert of the row is done to the same tanble, such that my execution of the scope_identity function will retrieve the identity value of the newer row.Gerry |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-07 : 10:45:05
|
quote:
Originally posted by gshacte
Thank you much. I am concerned that after asserting a row into the first table but before the procedure executes the scope_identity function to retrieve the identity value, that some other user might get to the point in the procedure where the assert of the row is done to the same tanble, such that my execution of the scope_identity function will retrieve the identity value of the newer row.Gerry
so do you mean to say you've a very busy environment with lot of concurrent executions? |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-07 : 14:52:28
|
| Far from a busy situation, probably 4-6 people using the system at a time. It is a data entry application, not mass updates.Gerry |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-08 : 04:34:15
|
| if concurrency is your problem why dont you start an explicit transaction inside your procedure for insert? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-08 : 11:27:54
|
quote:
Originally posted by gshacte
Thank you much. I am concerned that after asserting a row into the first table but before the procedure executes the scope_identity function to retrieve the identity value, that some other user might get to the point in the procedure where the assert of the row is done to the same tanble, such that my execution of the scope_identity function will retrieve the identity value of the newer row.Gerry
Again I'm confused. That's what SCOPE_IDENTITY() is designed for, so what is the issue? Perhaps I'm not understanding the issue...? |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-10 : 14:36:35
|
| I want to be sure that the id I get is from the row I inserted, not the id from a row someone may have added a moment later. After I add my row, but before my execution of the procedure has executed the scope_identity function, isn't there a possibility that someone else using the same procedure will have added another row, such that my execution of the scope_identity function will retrieve the id of the new row, not the one I added.Gerry |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-24 : 22:51:15
|
| I know its been a while. What I did is simply wrap my sql code in 'begin_transaction' and 'commit'. I think that will eliminate (I hope) the chance that any user can enter the code block while another user is already in the code block.Gerry |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-25 : 01:56:53
|
quote:
Originally posted by gshacte
I am concerned that after asserting a row into the first table but before the procedure executes the scope_identity function to retrieve the identity value, that some other user might get to the point in the procedure where the assert of the row is done to the same tanble, such that my execution of the scope_identity function will retrieve the identity value of the newer row.
It doesn't work like that. scope_identity() is in your scope, so will give you the identity that has just been assigned by your insert.So this scenario is OK:You insert a row (ID=1)Someone else inserts a row (ID=2) ... or many people do (ID=3,4,5,...)You check scope_identity() - you get ID=1.You must use scope_identity() in the same session as the Insert, but I imagine that is OK for you?INSERT INTO MyTable(Col1, Col2, ...) SELECT 'A', 'B', ...SELECT scope_identity() you don't need any Transaction Block (although it won't hurt  and there may be other good reasons for using a transaction block |
|
|
|
|
record id capture