Hello TD Gurus,
I'd like to lock the Teradata table(A). Read the data from table (A) and load all the data into Master table (B), delete all the records from table(A) and release the lock on table(A).
Why do I need to lock Table(A). Because table(A) is constantly updated and added with new records by the users community. So, during load and delete process we don't want to loose any record.
If lock is not suggested or advisable, so, pl. provide us with other best alternatives.
Thanks much for all your great advice.
You could use BT/ET, but it's better to do it within a "multistatement request", in a BTEQ script it's:
insert into target select * from source
;delete from source;
The multistatement is caused by the position of the first semicolon: if there's a new statement starting on the same line after the semicolon both statements are sent to TD as one block.
In a SP it's BEGIN REQUEST .... END REQUEST.
Neccessary locks (write on both tables) are automatically applied.