|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to get rowid of uncommitted recordauto-incrementing rowID in the header table, and required also in the detail table. It's a one-to-many relationship from the header table to the detail table. The code I have writing to these tables really needs to write the detail first, and then summariize and write the header infomation, however I can't write the detail until I get the HeaderID by writing a header record. So....If I write a record to the header table as a placeholder, how can I get the HeaderID of that record while I am writing it, to use it in the detail table (or for that matter reserve a HeaderID to be written later). It is the only unique ID in the header table, and I don't have anything else that I can uniquely use to go back and get the record just written to get the header table. Here's the further challenge - all of these are uncomitted transactions until both detail and header records for the transaction have been written, at which point I will commit them. Tell me there is some magic way to get the id of a record I am about to write, am writing, or have written. Help! Thanks! Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
Show quote > I have two tables - header, detail. HeaderID appears in both, is an What are you using to create the records? You should have no trouble getting > auto-incrementing rowID in the header table, and required also in the > detail table. It's a one-to-many relationship from the header table to > the detail table. > > The code I have writing to these tables really needs to write the detail > first, and then summariize and write the header infomation, however I > can't write the detail until I get the HeaderID by writing a header > record. > > So....If I write a record to the header table as a placeholder, how can I > get the HeaderID of that record while I am writing it, to use it in the > detail table (or for that matter reserve a HeaderID to be written later). > It is the only unique ID in the header table, and I don't have anything > else that I can uniquely use to go back and get the record just written to > get the header table. Here's the further challenge - all of these are > uncomitted transactions until both detail and header records for the > transaction have been written, at which point I will commit them. > > Tell me there is some magic way to get the id of a record I am about to > write, am writing, or have written. Help! > > Thanks! the rowid of the inserted header record. For instance, I myself use ADO to create a header record in a table inside a transaction, and pull back the identity value exactly the same way as if it wasn't in a transaction - I just read it from the column. I then create the detail lines to go with it, and at the end commit the transaction. If I have to rollback, both the header and the detail lines get rolled back. There should be no need to be doing anything special - this is exactly the same code I was using before I added a transaction (which was simply of case of adding a BeginTrans ADO Connection method call before the header record was created, and a CommitTrans method call at the end). Dan Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
command. the HeaderID is an identity colum increments by the DB. I've just never had to do this beofre so this is new terrirotry. I guess my question is how either when I am executing the command or after I execute it i can get the assigned headerID. Understand that this DB will process millions of trx/day, so I can't just write a record and go back and get the max id as something/someone else could have easly incremented it in the meantime. How are you going back to get the id regardless of it being within a transaction? Thanks! Show quote "Daniel Crichton" wrote: > Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800: > > > I have two tables - header, detail. HeaderID appears in both, is an > > auto-incrementing rowID in the header table, and required also in the > > detail table. It's a one-to-many relationship from the header table to > > the detail table. > > > > The code I have writing to these tables really needs to write the detail > > first, and then summariize and write the header infomation, however I > > can't write the detail until I get the HeaderID by writing a header > > record. > > > > So....If I write a record to the header table as a placeholder, how can I > > get the HeaderID of that record while I am writing it, to use it in the > > detail table (or for that matter reserve a HeaderID to be written later). > > It is the only unique ID in the header table, and I don't have anything > > else that I can uniquely use to go back and get the record just written to > > get the header table. Here's the further challenge - all of these are > > uncomitted transactions until both detail and header records for the > > transaction have been written, at which point I will commit them. > > > > Tell me there is some magic way to get the id of a record I am about to > > write, am writing, or have written. Help! > > > > Thanks! > > > What are you using to create the records? You should have no trouble getting > the rowid of the inserted header record. For instance, I myself use ADO to > create a header record in a table inside a transaction, and pull back the > identity value exactly the same way as if it wasn't in a transaction - I > just read it from the column. I then create the detail lines to go with it, > and at the end commit the transaction. If I have to rollback, both the > header and the detail lines get rolled back. There should be no need to be > doing anything special - this is exactly the same code I was using before I > added a transaction (which was simply of case of adding a BeginTrans ADO > Connection method call before the header record was created, and a > CommitTrans method call at the end). > > Dan > > > Matt wrote on Fri, 2 Dec 2005 08:31:07 -0800:
> Im using .NET SQLConnection and updating with a SQLcommand.executenonquery In ADO it's pretty simple - you just use a recordset Keyset cursor type with > command. the HeaderID is an identity colum increments by the DB. I've > just never had to do this beofre so this is new terrirotry. I guess my > question is how either when I am executing the command or after I execute > it i can get the assigned headerID. Understand that this DB will process > millions of trx/day, so I can't just write a record and go back and get > the max id as something/someone else could have easly incremented it in > the meantime. How are you going back to get the id regardless of it being > within a transaction? Thanks! a server-side cursor, write the values in to the new row, and after running the .Update method of the recordset read the value of the identity field. That's it. No messing around with making other calls back to SQL Server, or trying to determine the row id in some other arcane way. Dan Look at scope_identity() in Books Online.
It doesn't matter if the transaction is not committed. So, after you do your first insert: declare @headerID INT insert... set @headerID = scope_identity() Don't forget to handle errors. Show quote "Matt" <M***@discussions.microsoft.com> wrote in message news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@microsoft.com... >I have two tables - header, detail. HeaderID appears in both, is an > auto-incrementing rowID in the header table, and required also in the > detail > table. It's a one-to-many relationship from the header table to the > detail > table. > > The code I have writing to these tables really needs to write the detail > first, and then summariize and write the header infomation, however I > can't > write the detail until I get the HeaderID by writing a header record. > > So....If I write a record to the header table as a placeholder, how can I > get the HeaderID of that record while I am writing it, to use it in the > detail table (or for that matter reserve a HeaderID to be written later). > It > is the only unique ID in the header table, and I don't have anything else > that I can uniquely use to go back and get the record just written to get > the > header table. Here's the further challenge - all of these are uncomitted > transactions until both detail and header records for the transaction have > been written, at which point I will commit them. > > Tell me there is some magic way to get the id of a record I am about to > write, am writing, or have written. Help! > > Thanks! Thanks!
Show quote "Raymond D'Anjou" wrote: > Look at scope_identity() in Books Online. > It doesn't matter if the transaction is not committed. > So, after you do your first insert: > > declare @headerID INT > insert... > set @headerID = scope_identity() > > Don't forget to handle errors. > > "Matt" <M***@discussions.microsoft.com> wrote in message > news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@microsoft.com... > >I have two tables - header, detail. HeaderID appears in both, is an > > auto-incrementing rowID in the header table, and required also in the > > detail > > table. It's a one-to-many relationship from the header table to the > > detail > > table. > > > > The code I have writing to these tables really needs to write the detail > > first, and then summariize and write the header infomation, however I > > can't > > write the detail until I get the HeaderID by writing a header record. > > > > So....If I write a record to the header table as a placeholder, how can I > > get the HeaderID of that record while I am writing it, to use it in the > > detail table (or for that matter reserve a HeaderID to be written later). > > It > > is the only unique ID in the header table, and I don't have anything else > > that I can uniquely use to go back and get the record just written to get > > the > > header table. Here's the further challenge - all of these are uncomitted > > transactions until both detail and header records for the transaction have > > been written, at which point I will commit them. > > > > Tell me there is some magic way to get the id of a record I am about to > > write, am writing, or have written. Help! > > > > Thanks! > > >  |
|||||||||||||||||||||||
Other interesting topics