All Forums Tools
ursgovi 8 posts Joined 04/12
20 Jun 2012
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

Jim Chapman 449 posts Joined 09/04
20 Jun 2012
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.

ursgovi 8 posts Joined 04/12
21 Jun 2012

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.

jayashri 4 posts Joined 06/12
25 Jun 2012

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.

dnoeth 4628 posts Joined 11/04
25 Jun 2012

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

Dieter

Dieter

jayashri 4 posts Joined 06/12
25 Jun 2012

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
 

 

dnoeth 4628 posts Joined 11/04
25 Jun 2012

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

Dieter

ANUBHA 8 posts Joined 06/12
05 Jul 2012

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 

 

22 Dec 2015

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. 

BTEQDEVTEAM 9 posts Joined 04/11
23 Dec 2015

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

23 Dec 2015

BTEQ 14.10.00.00 Wed Dec 23 10:40:00 2015

 

 

 

 *** Logon successfully completed.

 *** Teradata Database Release is 14.00.04.03

 *** Teradata Database Version is 14.00.04.03

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 

 *** Total elapsed time was 1 second.

 

23 Dec 2015

.SHOW VERSIONS return following versions...
 

 

 BTEQ Version 14.10.00.00 for Linux running Socket TCP/IP

 CLIV2      : 14.10.00.00

 COPERR.H   : 14.10.00.03

 COPTYPES.H : 14.10.00.02

 DBCAREA.H  : 14.10.00.04

 DBCERR.H   : 13.10.00.00

 DBCHQEP.H  : 14.10.00.02

 MOSIIF.H   : 14.10.00.06

 PARCEL.H   : 14.10.00.15

 TERAGSS    : 14.10.00.00

 PIOM       : 14.10.00.00

 TDICU      : 14.10.00.00

 TDWALLET   : 14.10.00.00

 Database   : 14.00.04.03

 

BTEQDEVTEAM 9 posts Joined 04/11
23 Dec 2015

My apologies. I just figured out that you are not asking about BTEQ but rather about SQL -- and specifically the MERGE INTO statement. Someone else will need to respond to you on that. But you probably need to give more information... such as what your SQL request looks like and what success/failure messages you are getting back. You may also get more attention if you use the DATABASE forum instead of the TOOLS forum. Hope it all goes well for you.

24 Dec 2015

When i am using below merge into statement where i am trying to insert 200 resords and encounter with Referential integrity violation for 80 records and logged them into to error table with actual data.
i am wondering why the merge statement wouldn't continue and insert the error free of 180 records into my target table(DW_LOAD).it just simply roll back.
and also noticed if i have any data type  issues while inserting my merge statement is allowing error free records into Target table(DW_LOAD) and logging error records into error table but this time it not inserting only null values into all columns except "ETC_" columns.
But i am expecting to insert all error free records in RI violation case can you please advise how to achieve that.

CREATE ERROR TABLE DW_LOAD_ERROR FOR DW_LOAD;

MERGE INTO DW_LOAD
USING STG_FLOAD FL
ON FL.STG_PK = DW_LOAD.DW_PK
WHEN NOT MATCHED THEN INSERT
(FL.STG_PK,
FL.COL1,
FL.COL2,
FL.COL3,
FL.COL4)
LOGGING ALL ERRORS WITH NO LIMIT;

24 Dec 2015

will sure post in Database forum too..

ToddAWalter 316 posts Joined 10/11
24 Dec 2015

Was there another error code/message?
Did you Commit? If your session is in ANSI mode then a Commit is required.

You must sign in to leave a comment.