Teradata Default Column Value

Question

Hello,

I have created tables within Teradata that include the column 'TS_IMPORTED,' which is the Timestamp when the import occurs.  I have done this with HANA and it works, but when importing to Teradata it errs with the message that the column count differs.  Creating a field in Alteryx for 'TS_IMPORTED' fixes it, but the timestamp differs between records.  How do I import everything but the timestamp field, and let Teradata fill the default value?  I am using the Teradata Bulk Loader and the Output Data Tool from the 'In/Out' menu.

CREATE SET TABLE test
, FALLBACK
, NO BEFORE JOURNAL
, NO AFTER JOURNAL
, CHECKSUM = DEFAULT
, DEFAULT MERGEBLOCKRATIO
(
  ID          CHAR(7) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL
, HIERARCHY   VARCHAR(7) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL 
, VALID_FROM  DATE NOT NULL 
, VALID_UNTIL DATE NOT NULL 
, TS_IMPORTED TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6) NOT NULL
)
UNIQUE PRIMARY INDEX
(
  EID
, HIERARCHY
, VALID_FROM
);

PhilW · Answer

Simply exclude it from the Insert Columns List:

INSERT test (ID,HIERARCHY,VALID_FROM,VALID_UNTIL)VALUES (:ID,:HIERARCHY,:VALID_FROM,:VALID_UNTIL)

INSERT test (ID,HIERARCHY,VALID_FROM,VALID_UNTIL)SELECT ID,HIERARCHY,VALID_FROM,VALID_UNTIL FROM ...

kdkfjhasin · Answer

Thank you for the response, but no, it is external.  It uses the Input tool from the In/Out menu because it imports data from another database.

apathetichell · Answer

Is your Terradata connection coming in from In-DB? If so could you use something like the attached macro to select all of the fields from your db except the timestamp field?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Dynamic-Select-with-In-Database-Tools/ta-p/92212