check before insert
-
Hi, This is more for error proofing the flow of insert. Is there a way to check, before an insert if that particular order already exists in the table? we have a job that runs by the hour, and on each hour starts with an empty table. if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
-
Hi, This is more for error proofing the flow of insert. Is there a way to check, before an insert if that particular order already exists in the table? we have a job that runs by the hour, and on each hour starts with an empty table. if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
vanikanc wrote:
Is there a way to check, before an insert if that particular order already exists in the table?
Yes; the primary key. That's the one that uniquely identifies a tupel/record. Hence, that's what you'd need to check. Most databases will do this automatic and throw an error if the record already exists.
vanikanc wrote:
if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
Select a list of all primary key-values in the table, and skip those inserts.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Hi, This is more for error proofing the flow of insert. Is there a way to check, before an insert if that particular order already exists in the table? we have a job that runs by the hour, and on each hour starts with an empty table. if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
Wrap the process in a transaction. If it fails, it will get rolled back and the table will remain empty. At that point you can figure out what went wrong, take any remedial action and run the process again.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Wrap the process in a transaction. If it fails, it will get rolled back and the table will remain empty. At that point you can figure out what went wrong, take any remedial action and run the process again.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
I was planning to put BEGIN TRANSACTION and COMMIT TRANSACTION, but what I am doing is reading from a file, inserting into the database, reading the next line and inserting into database. I have the insert statement in the C# page.
Thsi post might be helpful: transactions with C# and class library[^] or this one: Transactions in .net[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Wrap the process in a transaction. If it fails, it will get rolled back and the table will remain empty. At that point you can figure out what went wrong, take any remedial action and run the process again.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
:thumbsup: Really good answer mark. This will stop the problem from ocurring in the first place. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
:thumbsup: Really good answer mark. This will stop the problem from ocurring in the first place. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Thanks.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
vanikanc wrote:
Is there a way to check, before an insert if that particular order already exists in the table?
Yes; the primary key. That's the one that uniquely identifies a tupel/record. Hence, that's what you'd need to check. Most databases will do this automatic and throw an error if the record already exists.
vanikanc wrote:
if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
Select a list of all primary key-values in the table, and skip those inserts.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
Dude, if he is inserting from a file, then there is no primary key until it is inserted :| -- Aah, good point. We don't show the Autoincrement-value to the user, so the user is using a combination of fields to uniquely identify a record. That used to be the primary-key, until we switched to artificial autoincrement-keys. You're reading the file on a line to line basis? Don't want it in memory completely, because it'd have to be restarted completely if the process dies half way. It'd be an option to write the "current amount of processed records" to another file. If it crashes, read that file and see how many lines you can safely skip. A transaction (as said below) is indeed the best idea :) Also, it'd be wise to load the file in a separate table first, and move it from there to the required structure.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Hi, This is more for error proofing the flow of insert. Is there a way to check, before an insert if that particular order already exists in the table? we have a job that runs by the hour, and on each hour starts with an empty table. if the process fails in the middle, and i want to start it again, if there are already some rows in the table, not to insert those.
If you are using SQL server 2008 look at the MERGE command. It will Update or Insert. As to the failure, as stated look at transactions or possibly Try Catch.
-
If you are using SQL server 2008 look at the MERGE command. It will Update or Insert. As to the failure, as stated look at transactions or possibly Try Catch.
-
Thank you. I am using the begin transaction/commit or rollback in catch block. Only thing, you have to specify the transaction as part of the sqlcommand object.
You can use the begin tran or try catch within the T-SQL. If you raise an error on failure you can then have C# rerun the data if that is what you want.