| Author |
Topic |
|
marchawinslet
Starting Member
11 Posts |
 Posted - 2006-08-17 : 11:08:45
|
| Here is a simplified version of my problem:I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully? Marcha x |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-17 : 11:16:27
|
| Check the cause for failing of the insert before executing the insert statement and correct it.(ie. Validate data before inserting)Srinika |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 11:53:48
|
and you really shouldn't care if the values are in order or if they have gaps.it's just a unique primary key and it should be treated as such. don't use it to number the data in your reports  Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
marchawinslet
Starting Member
11 Posts |
Posted - 2006-08-17 : 12:20:18
|
I'm getting the error message 'INSERT statement conflicted with COLUMN FOREIGN KEY constraint' if the user enters a 4-character ID into one of the foreign key columns that isn't in the joined table that contains these ID's. I understand this, that's not problem. In response to your sugestion, srinika, should my stored proc look for the existance of the character code in the joined table before trying the insert? There are more that 10,000 of these codes to check, mixed letters and numbers. Wouldn't it be faster to let it try to roll back if in insert fails (which may be almost never) rather than to do this check for every insert?spirit1 - I get your point.....user sees record ID 300 and assumes that there must be 300 records!Thanks for past and future suggestions.Marcha  |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-17 : 12:25:40
|
| just do if not exists (select * from yourtable where someCondtionToCheckForId)begin insertendGo with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
marchawinslet
Starting Member
11 Posts |
Posted - 2006-08-17 : 17:30:45
|
| Thanks...I'll do it, but I'm not 100% convinced that it's the most efficient method when erroneous data is likely to be rare! Also I have quite a few fields in the table that will need this type of check. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-17 : 17:42:21
|
quote:
Originally posted by marchawinslet
Thanks...I'll do it, but I'm not 100% convinced that it's the most efficient method when erroneous data is likely to be rare! Also I have quite a few fields in the table that will need this type of check.
Where is the application getting the bad data to try to insert?Why is the application not validating the input before executing the stored procedure?CODO ERGO SUM |
|
|
|
marchawinslet
Starting Member
11 Posts |
Posted - 2006-08-18 : 09:37:24
|
| The application validates the format, but not the value. It's a bit like not having your application check ALL valid postcodes (zip codes) to cut down on return trips to the server. This data is entered last thing in the evening from hundreds of sites by a few thousand people, but only one or two seem to get their Dept ID wrong. I would agree that the front end is where all validation should begin, but at 5pm every evening I think the server will just end up churning out Dept ID information to thousands of client apps and this might impact on the data inserts. Is this the case? Is there any way to assess the cost on system resources? ...or better still is there an efficient way to cache the ID numbers? So many questions....so little time! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-08-18 : 09:39:46
|
| you could store the values in the webservers application cache.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
marchawinslet
Starting Member
11 Posts |
Posted - 2006-08-19 : 19:07:36
|
| Thanks for all your help. Will try to find the most efficient solution.Marcha |
|
|
|
|
Unwanted Auto-incrementing