|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do I get Identity value of a record a've just inserted?I'm working on a VB.NET app and need to obtain the system-generated record
identity value in table A and update table B using this value. The problem is that I don't know the identity seed until the record is inserted into table A.Any help is greatly appreciated. Bill ---------------- Table A: linkedID Integer identity - seed 1 - 1 recname Char(10) Table B: linkedID (from table A) column2 column3 Use the SCOPE_IDENTITY function in the stored procedure that performs the
insert. To minimize the number of roundtrips you may want to insert to both tables from the same SP. -- David Portas SQL Server MVP -- David;
I just use a simple SQL to do the insert, not an SP. Can you please give me a hint on how to accomplish this (usin SCOPE_IDENTITY function) without using an SP? Thanks Bill Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:fuydnQfZkOcphU3fRVn-vg@giganews.com... > Use the SCOPE_IDENTITY function in the stored procedure that performs the > insert. To minimize the number of roundtrips you may want to insert to > both tables from the same SP. > > -- > David Portas > SQL Server MVP > -- > > First question is Why? Given the fundamental advantages of stored procs
(security, performance, maintainability, ease of maintenance, etc) you ought to have a very good reason NOT to use procs. Most of the time it pays to do ALL data access through procs. To do this without procs you can either return SCOPE_IDENTITY() as part of the batch that performs the INSERT or you can call @@IDENTITY. @@IDENTITY is slightly different to SCOPE_IDENTITY because it returns the last IDENTITY value inserted in a trigger (if any). If you don't have triggers on the table then you won't need to worry about this. If you are using connection pooling however, you can't rely on @@IDENTITY to return the correct IDENTITY value unless it is called in the same batch or proc as the INSERT itself. -- David Portas SQL Server MVP -- > First question is Why? Given the fundamental advantages of stored procs I emphatically second this. Add to this that we can upgrade to 2005 without > (security, performance, maintainability, ease of maintenance, etc) you > ought to have a very good reason NOT to use procs. Most of the time it > pays to do ALL data access through procs. any change to the user interface, but still taking full advantage of the new features and it just seems silly not to.. Too often this becomes a political battle rather than a battle of what is best -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:zrSdnSWwOcmVg03fRVn-tQ@giganews.com... > First question is Why? Given the fundamental advantages of stored procs > (security, performance, maintainability, ease of maintenance, etc) you > ought to have a very good reason NOT to use procs. Most of the time it > pays to do ALL data access through procs. > > To do this without procs you can either return SCOPE_IDENTITY() as part of > the batch that performs the INSERT or you can call @@IDENTITY. @@IDENTITY > is slightly different to SCOPE_IDENTITY because it returns the last > IDENTITY value inserted in a trigger (if any). If you don't have triggers > on the table then you won't need to worry about this. If you are using > connection pooling however, you can't rely on @@IDENTITY to return the > correct IDENTITY value unless it is called in the same batch or proc as > the INSERT itself. > > -- > David Portas > SQL Server MVP > -- > > You're both correct. I will need time to work on this, but it seemed to me
that this is the right direction to take. Thanks all Bill Show quote "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message news:%237uJCLNhFHA.572@TK2MSFTNGP15.phx.gbl... >> First question is Why? Given the fundamental advantages of stored procs >> (security, performance, maintainability, ease of maintenance, etc) you >> ought to have a very good reason NOT to use procs. Most of the time it >> pays to do ALL data access through procs. > > I emphatically second this. Add to this that we can upgrade to 2005 > without any change to the user interface, but still taking full advantage > of the new features and it just seems silly not to.. Too often this > becomes a political battle rather than a battle of what is best > > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:zrSdnSWwOcmVg03fRVn-tQ@giganews.com... >> First question is Why? Given the fundamental advantages of stored procs >> (security, performance, maintainability, ease of maintenance, etc) you >> ought to have a very good reason NOT to use procs. Most of the time it >> pays to do ALL data access through procs. >> >> To do this without procs you can either return SCOPE_IDENTITY() as part >> of the batch that performs the INSERT or you can call @@IDENTITY. >> @@IDENTITY is slightly different to SCOPE_IDENTITY because it returns the >> last IDENTITY value inserted in a trigger (if any). If you don't have >> triggers on the table then you won't need to worry about this. If you are >> using connection pooling however, you can't rely on @@IDENTITY to return >> the correct IDENTITY value unless it is called in the same batch or proc >> as the INSERT itself. >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> > > create a procedure:
CREATE PROCEDURE insertProcedure ( @column2 char(3), @column3 char(25), @column1 int output, @column4 rowversion output ) AS BEGIN DECLARE @_ROWCOUNT INT, @_ERROR INT, @_TRANCOUNT INT SET @_TRANCOUNT = @@TRANCOUNT BEGIN TRANSACTION INSERT tableName (column2, column3) VALUES (@column2, @column3) SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR SELECT @column1 = column1, @column4 = column4 WHERE column1 = SCOPE_IDENTITY() SELECT @_ERROR = @@ERROR @_ROWCOUNT = @@ROWCOUNT IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR COMMIT TRANSACTION RETURN 0 ERROR: IF @@TRANCOUNT > @@TRANCOUNT ROLLBACK TRANSACTION IF @_ERROR = 0 RETURN -1 RETURN @_ERROR END IN VB.NET, set load up the parameters and set the parameterdirection correctly, input, input/output, returncode, then execute the stored procedure. Show quote "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message news:edxWlHLhFHA.2444@tk2msftngp13.phx.gbl... > I'm working on a VB.NET app and need to obtain the system-generated record > identity value in table A and update table B using this value. The problem > is that I don't know the identity seed until the record is inserted into > table A.Any help is greatly appreciated. > > Bill > ---------------- > > Table A: > > linkedID Integer identity - seed 1 - 1 > recname Char(10) > > > Table B: > linkedID (from table A) > column2 > column3 > > > > > > > Brian;
This looks scary to me! Is this SP to be executed immdiately AFTER a successful INSERT in table A? many thanks. Bill Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23%23rg1VLhFHA.1444@TK2MSFTNGP10.phx.gbl... > create a procedure: > > CREATE PROCEDURE insertProcedure > ( > @column2 char(3), > @column3 char(25), > @column1 int output, > @column4 rowversion output > ) AS > BEGIN > DECLARE @_ROWCOUNT INT, @_ERROR INT, @_TRANCOUNT INT > SET @_TRANCOUNT = @@TRANCOUNT > BEGIN TRANSACTION > > INSERT tableName (column2, column3) VALUES (@column2, @column3) > SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR > > SELECT @column1 = column1, @column4 = column4 WHERE column1 = > SCOPE_IDENTITY() > SELECT @_ERROR = @@ERROR @_ROWCOUNT = @@ROWCOUNT > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR > > COMMIT TRANSACTION > RETURN 0 > > ERROR: > IF @@TRANCOUNT > @@TRANCOUNT ROLLBACK TRANSACTION > IF @_ERROR = 0 RETURN -1 > RETURN @_ERROR > END > > IN VB.NET, set load up the parameters and set the parameterdirection > correctly, input, input/output, returncode, then execute the stored > procedure. > > > "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message > news:edxWlHLhFHA.2444@tk2msftngp13.phx.gbl... >> I'm working on a VB.NET app and need to obtain the system-generated >> record >> identity value in table A and update table B using this value. The >> problem >> is that I don't know the identity seed until the record is inserted into >> table A.Any help is greatly appreciated. >> >> Bill >> ---------------- >> >> Table A: >> >> linkedID Integer identity - seed 1 - 1 >> recname Char(10) >> >> >> Table B: >> linkedID (from table A) >> column2 >> column3 >> >> >> >> >> >> >> > > Just trying to point you in the right direction. What's so scary about it?
It inserts a row and returns the newly generated identity and rowversion in output parameters. If the insert failed, it returns the error code in the sp return code. This way the same proc could be used by more than one app, or even by another sp. If you think this is scary, then the procedure to execute a sp that handles rows in more than one table would be much scarier: VB app creates temp table a, creates temp table b inserts rows into temp table a, inserts corresponding rows into temp table b. Executes stored procedure. SP starts transaction. inserts rows into table a. updates temp table b with new fk identity values from table a. updates temp table a with new pk identity and rowversion values from table a. inserts table b. updates temp table b with new identity and rowversion values from table b. commits the tran, returns 0 if no error. VB app reads new identity and rowversion values from temp tables a and b. You could have the vb app start the transaction, execute a sp for table a, then execute a separate sp for table b once for each corresponding row, and finally commit the transaction. But the performance is much better if you combine the inserts into set-based operations. Show quote "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message news:OqthFkLhFHA.3256@TK2MSFTNGP12.phx.gbl... > Brian; > > This looks scary to me! > > Is this SP to be executed immdiately AFTER a successful INSERT in table A? > > many thanks. > > Bill > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:%23%23rg1VLhFHA.1444@TK2MSFTNGP10.phx.gbl... > > create a procedure: > > > > CREATE PROCEDURE insertProcedure > > ( > > @column2 char(3), > > @column3 char(25), > > @column1 int output, > > @column4 rowversion output > > ) AS > > BEGIN > > DECLARE @_ROWCOUNT INT, @_ERROR INT, @_TRANCOUNT INT > > SET @_TRANCOUNT = @@TRANCOUNT > > BEGIN TRANSACTION > > > > INSERT tableName (column2, column3) VALUES (@column2, @column3) > > SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT > > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR > > > > SELECT @column1 = column1, @column4 = column4 WHERE column1 = > > SCOPE_IDENTITY() > > SELECT @_ERROR = @@ERROR @_ROWCOUNT = @@ROWCOUNT > > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR > > > > COMMIT TRANSACTION > > RETURN 0 > > > > ERROR: > > IF @@TRANCOUNT > @@TRANCOUNT ROLLBACK TRANSACTION > > IF @_ERROR = 0 RETURN -1 > > RETURN @_ERROR > > END > > > > IN VB.NET, set load up the parameters and set the parameterdirection > > correctly, input, input/output, returncode, then execute the stored > > procedure. > > > > > > "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message > > news:edxWlHLhFHA.2444@tk2msftngp13.phx.gbl... > >> I'm working on a VB.NET app and need to obtain the system-generated > >> record > >> identity value in table A and update table B using this value. The > >> problem > >> is that I don't know the identity seed until the record is inserted into > >> table A.Any help is greatly appreciated. > >> > >> Bill > >> ---------------- > >> > >> Table A: > >> > >> linkedID Integer identity - seed 1 - 1 > >> recname Char(10) > >> > >> > >> Table B: > >> linkedID (from table A) > >> column2 > >> column3 > >> > >> > >> > >> > >> > >> > >> > > > > > > Brian;
I'm not good in SQL syntax, and haven't played with advanced T-Transact before, that's why it looks "scary" to me. I'll apply your example to the VB app because I can see the benefit of using the SP for similar needs throughout the application. Many thanks Bill Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23AWb51NhFHA.2840@tk2msftngp13.phx.gbl... > Just trying to point you in the right direction. What's so scary about > it? > It inserts a row and returns the newly generated identity and rowversion > in > output parameters. If the insert failed, it returns the error code in the > sp return code. This way the same proc could be used by more than one > app, > or even by another sp. > > If you think this is scary, then the procedure to execute a sp that > handles > rows in more than one table would be much scarier: > > VB app creates temp table a, creates temp table b inserts rows into temp > table a, inserts corresponding rows into temp table b. Executes stored > procedure. > > SP starts transaction. inserts rows into table a. updates temp table b > with > new fk identity values from table a. updates temp table a with new pk > identity and rowversion values from table a. inserts table b. updates > temp > table b with new identity and rowversion values from table b. commits the > tran, returns 0 if no error. > > VB app reads new identity and rowversion values from temp tables a and b. > > > You could have the vb app start the transaction, execute a sp for table a, > then execute a separate sp for table b once for each corresponding row, > and > finally commit the transaction. But the performance is much better if you > combine the inserts into set-based operations. > > > "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message > news:OqthFkLhFHA.3256@TK2MSFTNGP12.phx.gbl... >> Brian; >> >> This looks scary to me! >> >> Is this SP to be executed immdiately AFTER a successful INSERT in table > A? >> >> many thanks. >> >> Bill >> >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:%23%23rg1VLhFHA.1444@TK2MSFTNGP10.phx.gbl... >> > create a procedure: >> > >> > CREATE PROCEDURE insertProcedure >> > ( >> > @column2 char(3), >> > @column3 char(25), >> > @column1 int output, >> > @column4 rowversion output >> > ) AS >> > BEGIN >> > DECLARE @_ROWCOUNT INT, @_ERROR INT, @_TRANCOUNT INT >> > SET @_TRANCOUNT = @@TRANCOUNT >> > BEGIN TRANSACTION >> > >> > INSERT tableName (column2, column3) VALUES (@column2, @column3) >> > SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT >> > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR >> > >> > SELECT @column1 = column1, @column4 = column4 WHERE column1 = >> > SCOPE_IDENTITY() >> > SELECT @_ERROR = @@ERROR @_ROWCOUNT = @@ROWCOUNT >> > IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR >> > >> > COMMIT TRANSACTION >> > RETURN 0 >> > >> > ERROR: >> > IF @@TRANCOUNT > @@TRANCOUNT ROLLBACK TRANSACTION >> > IF @_ERROR = 0 RETURN -1 >> > RETURN @_ERROR >> > END >> > >> > IN VB.NET, set load up the parameters and set the parameterdirection >> > correctly, input, input/output, returncode, then execute the stored >> > procedure. >> > >> > >> > "Bill nguyen" <billn_nospam_please@jaco.com> wrote in message >> > news:edxWlHLhFHA.2444@tk2msftngp13.phx.gbl... >> >> I'm working on a VB.NET app and need to obtain the system-generated >> >> record >> >> identity value in table A and update table B using this value. The >> >> problem >> >> is that I don't know the identity seed until the record is inserted > into >> >> table A.Any help is greatly appreciated. >> >> >> >> Bill >> >> ---------------- >> >> >> >> Table A: >> >> >> >> linkedID Integer identity - seed 1 - 1 >> >> recname Char(10) >> >> >> >> >> >> Table B: >> >> linkedID (from table A) >> >> column2 >> >> column3 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> > >> >> > >  |
|||||||||||||||||||||||
Other interesting topics