Exporting Data from Table to FLAT FILE using TERADATA SQL ASSISTANT is very simple. It is very easy to export data from any table in TERADATA using TERADATA SQL Assistant. First, you need to write SQL Query in Query Window. The output of this query you want in flat file. Go to FILE –> Export Results. Now when you run the query, it will display the browse window and will ask you to name the file in which output of the query will be re-directed.
SELECT COL1,COL2,COL3,COALESCE(COL4,0),COL5 from TABLENAME;
Points to consider while exporting data from table:
a) Delimiter: Whether you want file to be FIXED WIDTH or DELIMITED. If you want file to be delimited then you need to select the delimiter for the output file. To select the appropriate Delimiter for your output file go to TOOLS–>OPTIONS–> EXPORT/IMPORT –> Set the Delimiter.
b) FIXED WIDTH:If you want FIXED width file, then you have to CAST all columns as CHAR and assign the width to each column. All columns should be concatenated with each other to produce FIXED width file. Point to remember here; if there is any column in SELECT clause which is NULLABLE, then make sure to assign some default value for that column. Because NULL concatenates with anything make complete record NULL. So you may use COALESCE function in SELECT clause for NULLABLE columns.
c) DATE FORMAT:While exporting data from table using TERADATA SQL Assistant, it is very important to give proper format to DATE and TIMESTAMP columns. Since, TERADATA SQL Assistant converts date to its own internal format while displaying the data, it may export date columns in some other format, so make sure you apply CAST for date columns to specify proper format of date you want in exported file.
d) OPTIONS:There are other options available, like exporting SQL query in the exported file or exporting column headers etc. So check these options also while exporting data.
e) SUITABLE UTILITY:EXPORT via TERADATA SQL Assistant is not preferable if data is too huge in terms of records counts or row length. As it operates at row level, exporting huge volume of data may take long time. For such cases, use FASTEXPORT utility.