TPT Insert Select

Tools & Utilities
Teradata Employee

TPT Insert Select

Hi All,

 

I have been used mainly the FastExport and the FastLoad/MultiLoad. But I noticed that the TPT can load data directly with the following string.

 

===

USING CHARACTER SET UTF8
DEFINE JOB MIG_acTemplatExcelData
(
        DEFINE SCHEMA acTempletExcelData_SCHEMA ADJUST UNICODE
        (
                UserID CHAR(40),
                ExcelTempletID CHAR(40),
                TempletFileSize DECIMAL(18,0),
                EntryDateTime DECIMAL(14,0),
                BinData BLOB(2097088000) AS DEFERRED BY NAME
        );

        APPLY
        ( 'INSERT INTO PRD1.acTempletExcelData2
                (
                 :UserID,
                 :ExcelTempletID,
                 :TempletFileSize,
                 :EntryDateTime,
                 :BinData
                );'
        )
        TO OPERATOR ( $INSERTER() )

        SELECT * FROM OPERATOR
        (
         $SELECTOR ( acTempletExcelData_SCHEMA )
         ATTR( SelectStmt = 'SELECT * FROM PRD1.acTempletExcelData;' )
        );
);

===

 

Does this kind of script makes a text file which is selected data on an own client? Or it loads directly to the target table with memory or spool?

 

Regards,

quatre 


Accepted Solutions
Teradata Employee

Re: TPT Insert Select

Hi Fred,

 

Thank you, Fred.

The source and target system are different. The reason using SELECTOR and INSERTER is the source data is small and will not consume much free memory on source system.

 

Regards,

quatre

1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: TPT Insert Select

This will retrieve data from the SELECTOR operator to the TPT client, and use the INSERTER operator to send it back to the client in one step (with no intermediate file on the TPT client). 

But if source and target are the same system, it would be much more efficient to use the DDL operator to submit an actual INSERT/SELECT or MERGE statement. If it's two different systems, note that you could also use EXPORT in place of SELECTOR and LOAD (or UPDATE, or STREAM) in place of INSERTER.

Teradata Employee

Re: TPT Insert Select

Hi Fred,

 

Thank you, Fred.

The source and target system are different. The reason using SELECTOR and INSERTER is the source data is small and will not consume much free memory on source system.

 

Regards,

quatre