Insert row by row

Question

Insert row by row

joemai

Hall of Fame

Points: 3496
More actions
November 23, 2007 at 10:58 am

#70282

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003918 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abmore Hall of Fame Points: 3628 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1003918 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)