How to capture the rejected rows from BTEQ.IMPORT

Tools & Utilities

How to capture the rejected rows from BTEQ.IMPORT

I have a bteq script that uses import command to load data from a text file to a Teradata table.

.set quiet on;
.import vartext ',' file = \\MyServer\Proj\filename.txt
.repeat *

using
BUSINESS_TYPE (varchar(255)),
ACCOUNT_NB (varchar(255)),
CNTRCT_DT (varchar(255))

INSERT INTO ZALTS1RV.ZALV281_QRMEUR_DRO
( BUSINESS_TYPE ,
ACCOUNT_NB ,
CNTRCT_DT
)
VALUES
( :BUSINESS_TYPE ,
:ACCOUNT_NB ,
:CNTRCT_DT );

The script ran successfully and gave the output message :

*** Finished at input row 5455 at Wed Feb 10 15:03:50 2010
*** Total number of statements: 5455, Accepted : 5429, Rejected : 26

Is there anyway to capture those rejected rows and write into another file ?

Thanks

2 REPLIES
Enthusiast

Re: How to capture the rejected rows from BTEQ.IMPORT

I dont know if Bteq supports pre defined variables like multiload SYSDATE, SYSTIME, SYSAPLYCNT. But you can use

.SET REPEATSTOP ON

to stop proceeding with the import.
bwb
Teradata Employee

Re: How to capture the rejected rows from BTEQ.IMPORT

Q: Is there anyway to capture those rejected rows and write into another file ?

A: No, sorry, not with BTEQ. If you have to capture the rejected rows, you'll have to use one of the load utilities (i.e., FastLoad; MultiLoad; TPump; or the corresponding TPT operators, Load, Update, or Stream). The rejected rows (or, in some cases, just the input file number and record number) will be captured in error table(s).

As enjoycoding stated, .SET REPEATSTOP ON will abort the REPEAT at the first error, but it doesn't sound like that's what you want.

Of course, the ultimate answer is to clean up the incoming data (I realize that you may not have control over that, but in the long run that's the most efficient approach).