CREATE ERROR TABLE FOR LOGGING

Database
Enthusiast

CREATE ERROR TABLE FOR LOGGING

I have created an error table for my table, so that the DML statements doesn't abort; the bad row (rows which could not be inserted/updated in my table due to reasons such as bad format in a column or any constraint etc.) gets inserted in the error table.

Please check the link below to have a better understanding of what I am trying to say:

http://forgetcode.com/Teradata/1537-CREATE-ERROR-TABLE-Detailed

The above mentioned logic is working absolutely fine. The issue is, row limit is set in the error table (i.e. maximum 10 rows could be inserted in a single run). If I am loading 1000s of records in 1 run, there are chances that more than 10 records are erroneous records.

Is there any way/method which can change the row limit of the error table?

This is what I have:

--Create a table which will have all the records (including errooneous records)
CREATE MULTISET TABLE table_old ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
i INTEGER,
j VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( i );

--Insert all the records in the created table
INSERT table_old (i, j) VALUES (1,'abc');
INSERT table_old (i, j) VALUES (2,'def');
INSERT table_old (i, j) VALUES (5,'ghi');
INSERT table_old (i, j) VALUES (6,'abc');
INSERT table_old (i, j) VALUES (7,'def');
INSERT table_old (i, j) VALUES (8,'ghi');
INSERT table_old (i, j) VALUES (9,'abc');
INSERT table_old (i, j) VALUES (10,'def');
INSERT table_old (i, j) VALUES (11,'ghi');
INSERT table_old (i, j) VALUES (12,'abc');
INSERT table_old (i, j) VALUES (13,'def');
INSERT table_old (i, j) VALUES (14,'ghi');
INSERT table_old (i, j) VALUES (15,'abc');
INSERT table_old (i, j) VALUES (16,'def');
INSERT table_old (i, j) VALUES (1,'012');
INSERT table_old (i, j) VALUES (1,'345');
INSERT table_old (i, j) VALUES (2,'999');
INSERT table_old (i, j) VALUES (3,'ghi');
INSERT table_old (i, j) VALUES (1,'111');
INSERT table_old (i, j) VALUES (2,'222');
INSERT table_old (i, j) VALUES (4,'444');
INSERT table_old (i, j) VALUES (3,'ab1');
INSERT table_old (i, j) VALUES (2,'221');
INSERT table_old (i, j) VALUES (3,'12d');

--Create the table having constraint in a column

CREATE MULTISET TABLE table_new_s ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      i INTEGER,

      j VARCHAR(125) CHARACTER SET LATIN NOT CASESPECIFIC,

      CONSTRAINT CHK CHECK ( ((i  IN (1 ,2 )) AND  ((UPPER((TRIM(BOTH  FROM j )))(CASESPECIFIC)) = (LOWER((TRIM(BOTH  FROM j )))(CASESPECIFIC)))) OR  (i  IN (3 ,4 )) )

      --Constraint: If i has value 1 or 2 then value of j should be numeric else j could be anything (varchar/numeric)

     )

PRIMARY INDEX ( i );

--Create the error table for table table_new_s

CREATE ERROR TABLE FOR table_new_s;

--Insert records in table_new_s

INSERT INTO table_new_s SELECT * FROM table_old LOGGING ALL ERRORS;

Please advice.

Thanks,

Sneha

S Gupta
2 REPLIES
Teradata Employee

Re: CREATE ERROR TABLE FOR LOGGING

INSERT INTO table_new_s SELECT * FROM table_old LOGGING ALL ERRORS WITH NO LIMIT;
Enthusiast

Re: CREATE ERROR TABLE FOR LOGGING

Thanks a lot @AtardecerR0j0

It's working :)

S Gupta