Error handling when using set-based processing
-
Good morning fellow SQL-ers...
I've got a stored procedure that runs three times per day. Maintenance workers on the floor enter work order information into a legacy system. The stored procedure takes that data and puts it into several tables in a SQL Server db.
Most of the time it works without a hitch but periodically it will error out because the worker is not careful about how he/she enters data into the legacy system. What happens is that the bad data causes a primary key violation.
My question... when using set based processing, is it possible to catch the rows that would violate the PK and write them to, say, a temp table and it would then process the remainder of the rows?
I'm guessing that the only way to do this is by some sort of row-by-row processing. Am I correct?
If it helps, here is the insert statement in question:
INSERT INTO WOE (WONUM, CLOSEDATE, EQNUM, LOCATION, SUBLOCATION1, SUBLOCATION2, SUBLOCATION3, EMPCODE, CRAFT, WODATE, LASTNAME, FIRSTNAME, ESTHRS, REGHRS, OTHRS, OTFACTOR, WAGECODE, RATE, UPDATESTAMP, UPDATEUSER, SITEID, CONVNUM)
SELECT A.BMP2WO, A.BCYMD, B.EQNUM, B.LOCATION, B.SUBLOCATION1, B.SUBLOCATION2, B.SUBLOCATION3, A.BCLOCK, C.CRAFT, A.BCYMD, C.LASTNAME, C.FIRSTNAME, NULL AS ESTHRS, A.BHOURS, NULL AS OTHRS, '1.5' AS OTFACTOR, 'REG HRS' AS WAGECODE, E.RATE, NULL AS UPDATESTAMP, NULL AS UPDATEUSER, 'BRILLION-FDY' AS SITEID, NULL AS CONVNUM
FROM MP2BATF A, WOEQLIST B, EMP C, WO D, EMPWAGE E
WHERE A.BMP2WO = B.WONUM
AND A.BMP2WO = D.WONUM
AND A.BCLOCK = C.EMPCODE
AND B.WONUM = D.WONUM
AND B.CLOSEDATE = D.CLOSEDATE
AND B.SITEID = D.SITEID
AND A.BCLOCK = E.EMPCODE
AND A.BCOMP = 'Y'
Any suggestions would be greatly appreciated!
Bob
-
With a set-based insert, the entire insert will succeed, or will fail. It's not possible to insert the rows that are OK and ditch the others.
If this is a common problem, what you can do is something like this.
INSERT INTO ErrorsTable
SELECT <fields> FROM <tables>
WHERE <filters> AND <pk value> IN (SELECT <pk field> FROM WOE)
INSERT INTO WOE (...)
SELECT <fields> FROM <tables>
WHERE <filters> AND <pk value> NOT IN (SELECT <pk field> FROM WOE)
That way you insert all the rows that won't have pk violations into the actual table and all the rows that would have caused an error get inserted somewhere else for further processing.
By the way, try and do your joins in the FROM, not the WHERE clause, for easier reading, if nothing else.
eg SELECT .. FROM TableA a INNER JOIN TableB b ON a.ID = B.aID ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
...try and do your joins in the FROM... With apologies to Shakespeare, but could not resist
ANSI-92, or not ANSI-92, that is the question
Whether 'tis nobler in the mind to suffer
The slings and arrows of furious DBA's
Or to take arms against a sea of troubles
And by opposing end them? To fail and weep
Far away is close at hand in the images of elsewhere.
Anon. -
Gila's given you the correct approach. If you are doing a LOT of rows you may wish to break the transaction into several smaller batches. Something like
1. insert into key_violations...
2. while exists(select * from where )
begin
begin transaction
insert into dest_table... select top 10000 ... from source_tables....
if @@ERROR = 0
delete from source_table
inner join (select top 10000 pkeys from source_table) source_table_10000
on source_table.pkey = source_table_10000.pkey
else
--flag some error (shouldn't happen unless you run out of space or there are other users with locks - but
-- this is still important to catch!!)
commit transaction
end
Not perfect code (it's late here) but might help on large inserts
-
I'm sure the bard is rolling in his grave right about now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply