I am writing a code to change PI of an existing table.
Following is the code snippet :
create table person_bck (person_id bigint not null , person_name varchar(100)) primary index (person_id);
insert into person_bck (person_id , person_name ) select person_id , person_name from person;
drop table person;
rename table person_bck to person;
This code executes fine but fails with the following error when i surround it with bt - et.
Statement 2: INSERT Failed. 3932: Only an ET or null statement is legal after a DDL Statement.
I want to write this code as part of a single transaction; if any of the statement fails, it rolls back everything.
Some posts suggested to commit after the create table command and end transaction. then do rest work in another transaction.
However, in that case the rollback of create table will not happend because its a separate unit altogether.
How can i achieve this in a single atomic transaction unit?
In BTET mode each individual statement is treated as a transaction, to combine multiple statements in transaction you need to use BTET. In case of DDL it is a requirement that each DDL statement must be followed by a COMMIT in case of ANSI mode. but in Teradata Mode I didn't find any such restriction. Might be changing the transaction mode will work for you. Else you will have to use the ET after each DDL to accomplish this. Or you can use some procedure to accomlish the task in entirety.