Teradata FastLoad and Fast Export

Teradata Fast Load Utility is used to load data from text files into empty tables.

Example Create a text file with the following records and name the file as itemsalessummary.txt.

101,1234,2017-09-05,36755.10 101,1235,2017-09-05,12345.11 101,1236,2017-09-05,23456.22 101,1237,2017-09-05,34567.33 102,1234,2017-09-05,45678.44

Following is a sample FastLoad script to load the above file into Item_Sales_Summary table.

Save it as ItemSalesLoad.fl

SESSIONS 2; ERRLIMIT 1000; logmech LDAP; LOGON 123.123.12.123/c123456,password;

DROP TABLE DatabaseName.Item_Sales_Summary ; DROP TABLE DatabaseName.Item_Sales_Summary_Err1; DROP TABLE DatabaseName.Item_Sales_Summary_Err2;

CREATE MULTISET TABLE DatabaseName.Item_Sales_Summary_LT ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( Item VARCHAR(6), StoreNumber SMALLINT, Date DATE, TotalSales SMALLINT ) UNIQUE PRIMARY INDEX (Item,StoreNumber);

SET RECORD VARTEXT "|" DISPLAY_ERRORS NOSTOP;

DEFINE

Item (VARCHAR(75)) StoreNumber (VARCHAR(75)) Date (VARCHAR(75)) TotalSales (VARCHAR(75), NULLIF = 'NULL')

FILE=C:\Users\Documents\itemsalessummary.txt;

SHOW;

BEGIN LOADING DatabaseName.Item_Sales_Summary_LT

ERRORFILES DatabaseName.Item_Sales_Summary_LT_Err1, DatabaseName.Item_Sales_Summary_LT_Err2

CHECKPOINT 50000;

Record 2;

INSERT INTO DatabaseName.Item_Sales_Summary_LT ( Item ,StoreNumber ,Date ,TotalSales ) VALUES ( :Item ,:StoreNumber ,:Date ,:TotalSales );

END LOADING;

LOGOFF; Executing a FastLoad Script you can run the FastLoad script using the following command in UNIX or Windows command prompt.

fastload<c:\users\documents\ItemSalesLoad.fl

Once the above command is executed, the FastLoad script will run and load the data to the table

Teradata Fast Export Utility is used to export data from Teradata tables into flat files.

Example: Save the file as abcfymonth.fx .logtable DatabaseName.utillog; .logmech LDAP; .LOGON 123.123.12.123/c123456,Password; DATABASE DatabaseName; .BEGIN EXPORT SESSIONS 2; .EXPORT OUTFILE C:\Users\Documents\data.txt MODE RECORD FORMAT TEXT; SELECT CAST(FY_YEAR_MONTH AS CHAR(6)), CAST(STORE AS CHAR(6)), CAST(TRANS_TYPE AS CHAR(30)), CAST(GC_NUMBER AS CHAR(20)), CAST(BIN AS CHAR(70)), CAST(ACCTG_GROUP AS CHAR(50)), CAST(DOLLAR_AMOUNT AS CHAR(25)) FROM ABC_FY_MONTH; .END EXPORT; .LOGOFF;

Run the below command in cmd prompt to execute the script

fexp <C:\users\documents\abcfymonth.fx

Output file will be created and you can use Access or SAS to open the huge file.