|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to insert ?I want to insert about 10000 records into database.
I don't know if the records existed in the database at first. There are to methods to achieve it. 1. Use select to test if primary key of a record existed in the database, if not, use insert command to insert the record. 2. Do not test, just use the insert command to insert the record, if the record existed, the Exception will be raised, ignore the exception. Which method is better? In general, I try to avoid exceptions so would opt for #1
A third option (depending on row size) would be to create a temp table with the same structure as the target table, insert all 10000 records into the temp table and then insert f1, f2, fn from temp where temptablepk not in ( select targetpk from targettable ) then finally drop the temp table. Once all the records have been inserted into the temp table, no more traffic on the wire until insert completes. Show quote "ad" <fly***@wfes.tcc.edu.tw> wrote in message news:Oi7uZJVjGHA.1320@TK2MSFTNGP04.phx.gbl... >I want to insert about 10000 records into database. > I don't know if the records existed in the database at first. > There are to methods to achieve it. > 1. Use select to test if primary key of a record existed in the database, > if not, use insert command to insert the record. > 2. Do not test, just use the insert command to insert the record, if the > record existed, the Exception will be raised, ignore the exception. > > Which method is better? > > > Thank for your answer.
Why you try to avoid exceptions ? Could you give me some reasons? Show quote "Jim Hughes" <NOSPAMJ3033@Hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:Or8q0iXjGHA.4***@TK2MSFTNGP05.phx.gbl... > In general, I try to avoid exceptions so would opt for #1 > > A third option (depending on row size) would be to create a temp table > with the same structure as the target table, insert all 10000 records into > the temp table and then insert f1, f2, fn from temp where temptablepk not > in ( select targetpk from targettable ) then finally drop the temp table. > > Once all the records have been inserted into the temp table, no more > traffic on the wire until insert completes. > > "ad" <fly***@wfes.tcc.edu.tw> wrote in message > news:Oi7uZJVjGHA.1320@TK2MSFTNGP04.phx.gbl... >>I want to insert about 10000 records into database. >> I don't know if the records existed in the database at first. >> There are to methods to achieve it. >> 1. Use select to test if primary key of a record existed in the database, >> if not, use insert command to insert the record. >> 2. Do not test, just use the insert command to insert the record, if the >> record existed, the Exception will be raised, ignore the exception. >> >> Which method is better? >> >> >> > > Because exceptions degrade performance of an application.
A Google search will turn up many detailed explanations. http://www.google.com/search?hl=en&q=vb.net+avoid+exceptions&btnG=Google+Search Show quote "ad" <fly***@wfes.tcc.edu.tw> wrote in message news:esWOtTZjGHA.4040@TK2MSFTNGP05.phx.gbl... > Thank for your answer. > > Why you try to avoid exceptions ? > Could you give me some reasons? > > > > "Jim Hughes" <NOSPAMJ3033@Hotmail.com> > ¼¶¼g©ó¶l¥ó·s»D:Or8q0iXjGHA.4***@TK2MSFTNGP05.phx.gbl... >> In general, I try to avoid exceptions so would opt for #1 >> >> A third option (depending on row size) would be to create a temp table >> with the same structure as the target table, insert all 10000 records >> into the temp table and then insert f1, f2, fn from temp where >> temptablepk not in ( select targetpk from targettable ) then finally drop >> the temp table. >> >> Once all the records have been inserted into the temp table, no more >> traffic on the wire until insert completes. >> >> "ad" <fly***@wfes.tcc.edu.tw> wrote in message >> news:Oi7uZJVjGHA.1320@TK2MSFTNGP04.phx.gbl... >>>I want to insert about 10000 records into database. >>> I don't know if the records existed in the database at first. >>> There are to methods to achieve it. >>> 1. Use select to test if primary key of a record existed in the >>> database, if not, use insert command to insert the record. >>> 2. Do not test, just use the insert command to insert the record, if the >>> record existed, the Exception will be raised, ignore the exception. >>> >>> Which method is better? >>> >>> >>> >> >> > > In addition to Jim's reply:
Exceptions are exceptions - something out of your control and shouldn't be abused. -- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "ad" <fly***@wfes.tcc.edu.tw> wrote in message news:esWOtTZjGHA.4040@TK2MSFTNGP05.phx.gbl... > Thank for your answer. > > Why you try to avoid exceptions ? > Could you give me some reasons? > > > > "Jim Hughes" <NOSPAMJ3033@Hotmail.com> > ¼¶¼g©ó¶l¥ó·s»D:Or8q0iXjGHA.4***@TK2MSFTNGP05.phx.gbl... >> In general, I try to avoid exceptions so would opt for #1 >> >> A third option (depending on row size) would be to create a temp table >> with the same structure as the target table, insert all 10000 records >> into the temp table and then insert f1, f2, fn from temp where >> temptablepk not in ( select targetpk from targettable ) then finally drop >> the temp table. >> >> Once all the records have been inserted into the temp table, no more >> traffic on the wire until insert completes. >> >> "ad" <fly***@wfes.tcc.edu.tw> wrote in message >> news:Oi7uZJVjGHA.1320@TK2MSFTNGP04.phx.gbl... >>>I want to insert about 10000 records into database. >>> I don't know if the records existed in the database at first. >>> There are to methods to achieve it. >>> 1. Use select to test if primary key of a record existed in the >>> database, if not, use insert command to insert the record. >>> 2. Do not test, just use the insert command to insert the record, if the >>> record existed, the Exception will be raised, ignore the exception. >>> >>> Which method is better? >>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics