November 30, 2010 at 3:45 pm
I have a script with nearly 1000 update statements.
Soem of them are not properly executed since they encounter the primary key violation error.
Is there a way i could execute everything that is not errored out.
That is if a random 50 of those 1000 statements end up in errors, how can i skip those n proceed to the ones next.
Thank you.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
November 30, 2010 at 4:09 pm
If possible for you could you please post the basic concept of your sproc?
Reason: Having a single sproc with a 1000 update statements calls for a review. Either the sproc should be deivided into sub-sprocs or there is a way to compress the code for those numerous updates, e.g. using dynamic SQL.
The worst case scenario (and most probably the easiest to fix) would be some kind of a loop (W.H.I.L.E or c.u.r.s.o.r.).
So we'd like to know the general structure of your sproc to get a feeling in what direction to proceed.
December 2, 2010 at 2:58 am
Don't use BEGIN TRANSACTION and COMMIT, it will rollback if an error occurs.
If you use Management Studio put GO (batch separator) between each lines.
INSERT ...
GO
INSERT ...
GO
...
If you use vbscript or something, try On Error Resume Next (or similar) to skip errors.
Or create your primary key with IGNORE_DUP_KEY:
CREATE TABLE [t](id INT,
CONSTRAINT [PK_t] PRIMARY KEY NONCLUSTERED
([d] ASC) WITH (IGNORE_DUP_KEY = ON)
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply