CODEWITHSUNDEEP
database-connectionteradata
Jit B
Jit B

Reputation: 1246

Teradata: Difference between ANSI and TERA modes with respect to record insertion

I am working on an application that takes records from Hadoop and inserts them into Teradata via sqoop(JDBC).

I am using TERA mode for the connection and the target table is SET table. I am getting duplicate row issue for some datasets.

As far as I know TERA mode is supposed to ignore duplicate records while doing inserts. Can somebody please confirm the behavior?

Upvotes: 0

Views: 3192

Answers (1)

Mukesh S
Mukesh S

Reputation: 2896

For a SET table in TERA mode in Teradata there are two scenarios:

1.) For set table table_name(which throw Duplicate Row Exception):

insert into DBName.table_name(id,name) values(1,'Mukesh'); //Success

insert into DBName.table_name(id,name) values(1,'Mukesh'); //Failure

insert into DBName.table_name(id,name) values(1,'mukesh'); //Failure

In TERA mode it is not case sensitive, so it treats the records as same. And also throw Duplicate Row Exception.

2.) For set table table_name(which do not throw Duplicate Row Exception):

insert into DBName.table_name(id,name) select id,name from DBName.table_name2;

If there are records in DBName.table_name2 which are already in table DBName.table_name and if we try to execute the above query, it will simply ignores the Duplicate Row Exception and also it will not insert anything it the table DBName.table_name.

Also in ANSI mode, there will be Duplicate Row Exception for both the above scenarios.

Upvotes: 2

Related Questions