Can anyone explain me whether TPT Stream Operator(TPUMP) can be used to load data from file to table and table to file. If it is possible could you kindly provide the script for the same.
In my view, TPT Stream Operator is best suitable for mini batch loads. Could you confirm this also please.
The Stream operator just sends data to TD; in a TPT job the Data Connector Producer operator reads from files. Stream does not read from tables. See the manuals for examples.
I would say there is some overlap between use cases for mini-batch and use cases for Stream, but would stop well short of saying that's the best use for Stream, let alone that Stream was necessarily the best choice for mini-batch.
The idea of mini-batch is to load into a staging table, and then perform an INS-SEL or MERGE-INTO into the target table.
For this type of mini-batch, the Stream operator is actually the wrong operator to use. A user would typically use the Load operator to load the data into the staging table (because most staging tables are empty), and then issue the INS-SEL/MERGE-INTO SQL. Both of these operations can be performed by TPT as well.
As to file-to-table and table-to-file, yes TPT is able to perform that and there are several options for operators that can be used. For "file-to-table" you use the DataConnector operator for the file reader, and then you can pick from the Load, Update, Stream or Inserter operator to load the data into Teradata. For "table-to-file", you use the DataConnector operator as the file writer, and then you use the Export or Selector operator to extract the data out of Teradata.
The TPT User Guide has examples of these scenarios. And in the installation directories for TPT, there are sample scripts that you can reference.