November 23, 2007 at 10:58 am
Hi All,
I want to insert data from a temp table into a base table row by row and capture the error of each specific row if it has problem. For the rows that have no problem, they can be inserted successfully into the base table. Is that possible? Normally, I do:
insert into base_table
select * from temp_table
But that no longer work for me in my environment. I need to capture the error of each single row.
Please advice
Many thanks for your help.
Minh Vu
November 23, 2007 at 11:48 am
Sure, you can use a cursor or while loop to perform such a feat...
But that also means you're doing one of the worst things possible in any RDBMS... you're programming by exception. If the data is already in a staging table, it would be much more effective if you examined the data in the staging table to figure out what is good data and bad before doing the insert. You could even mark each row in the staging table as to the reason something was bad and move the rows to a "bad row holding table" at the end of a run.
And, none of it has to be RBAR...
--Jeff Moden
November 23, 2007 at 9:50 pm
Jeff is correct use CURSOR for row by row processing but, remember there is a problem using curosr it will affect slower down the insertion.
so better you first validate the records from tmp tbale dump the error row number Or error row in tmpErroLog and then show the Errorlog.
November 23, 2007 at 10:12 pm
No, no... (just making sure)... I said you "could" use a cursor... it wasn't a recommendation. 😀
Perhaps an example of the type of validation I'm talking about is required...
So far as an example goes, sure... here's a simple one...
Let's say that you have a column called ZipCode in both the staging table and the target table. The target table contains a constraint that says each zip code must be 5 digits or 9 digits. The staging table has a zip with only 6 digits in it. If you try to insert that into the target table, you will get an error for the row. If you're lucky, you'll know what the error is.
BUT, if you run the following code on your staging table, you'll know what the error is before you try the insert into the target table... for the whole table! You'll validate all the zipcodes in the staging table, all at once...
UPDATE stagingtable
SET Error = ISNULL(Error+',','')+'Bad Zip Len'
WHERE LEN(ZipCode) NOT IN (5,9)
Only those items with a NULL in the Error column of the staging table would be inserted into the target table. And, they would all be inserted with NO ERRORS. The staging table would have a list of rows and all the errors.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply