BTEQ - Row Level COMMIT

Tools & Utilities
Enthusiast

BTEQ - Row Level COMMIT

Hi all,

I've a requirement where I'm using BTEQ to move a copy of data from Table A to Table B.  I'm using a simple

INSERT INTO Table B SELECT * FROM Table A;

Note: Table A and Table B are with same structure. 

Assuming I've 100000 rows in Table A and during execution of my INSERT.. SELECT query, 90000 rows got inserted successfully.  Due to some error (data error) in 90001st row, my BTEQ fails.  By default, when BTEQ fails, ROLLBACK occurs. I dont want that to happen.

I would like to know if there's any way, by which, those 90000 rows can still be retained in Table B, so that I dont have to reload them again and just restart from 90001st row. If you can help me with a sample code, it would be really great. 

Thanks,

Govi

Tags (2)
15 REPLIES
Enthusiast

Re: BTEQ - Row Level COMMIT

create error table for Table_B ;

insert into Table_B select * from Table_A
    logging errors ;

This isn't quite what you asked for, as it will automatically continue inserting rows from the source even if a data error occurs.  Only rows that cause an error will be rejected and placed in the error table.

Enthusiast

Re: BTEQ - Row Level COMMIT

Thanks Jim.  This is something which I was not aware of. 

Does the error table receive actual data in case of any data error? If yes, then I see a possibility tat this will work.

Re: BTEQ - Row Level COMMIT

I am trying log records in error table,

My script is like below:

Delete from MMDM_WORK.test_tgt_rej;

create error table for MMDM_WORK.test_tgt_rej ;

INSERT INTO MMDM_WORK.test_tgt_rej

SELECT

id,name from MMDM_WORK.test_src_rej;

logging errors ;

.logoff

I am getting below error

logging errors ;

logging errors ;

       $

 *** Failure 3706 Syntax error: Expecting the word ONLINE.

                Statement# 1, Info =9

 *** Total elapsed time was 1 second.

Junior Contributor

Re: BTEQ - Row Level COMMIT

There's n semicolon before "logging errors", it's part of the Insert/Select.

Dieter

Re: BTEQ - Row Level COMMIT

Thanks. Now script is excuted. But I am unable to get records which are not able to insert into table due to data type mismatch

Source

id          name

3           XYZ

1           ART

B1         Exception

A1         Shweta

2           ABC

id-varchar

name-varchar

target

id -Integer

name-varchar

I want to capture records that cant't be inserted in the target table due to data type conversion not possible..

Is there any way to get such records

Junior Contributor

Re: BTEQ - Row Level COMMIT

If the PK columns (id?) of your table fail the type conversion you're out of luck.

Otherwise you could switch to MERGE instead of INSERT/SELECT:

merge into test_tgt_rej as tgt using test_src_rej AS src

on tgt.name = src.name

when not matched

then insert(src.id,src.name)

logging errors;

Dieter

Enthusiast

Re: BTEQ - Row Level COMMIT

method 1:

with the following query

create error table for settablewith100rows3;

insert into settablewith100rows3

select * from settablewith100rows2

logging errors;

we able to create a error table ET_settablewith100rows3 and all the failed records are saved to this error table. but the row which is having problem is not displayed properly.

ET table has many columns

Method 2:

with the following query

merge into settablewith100rows3 as tgt using settablewith100rows AS src

on tgt.col2 = src.col2

when not matched

then insert(src.col1,src.col2)

logging errors;

i am able to find the error 3812:  The positional assignment list has too few values.

---------------------------------

how the correct records save to target table and failed records save to error table. Please give the query 


Enthusiast

Re: BTEQ - Row Level COMMIT

me either tried Merge into statement to perform inserts if no match .for me out of 250 records 100 records written to error table which are failed to  due to  Ri volation and rest 150 was not inserted which are suppose to continue inserts into target table.please advise. 

Teradata Employee

Re: BTEQ - Row Level COMMIT

Please post the versions of the DBS and BTEQ being used. To get that, you can logon via BTEQ and issue this command:      .SHOW VERSIONS