I am using Fast export to copy a Teradata table to a flat file on a windows server.
.logtable database.logtable_tablename; .logon terdata_box/user_id,password; .begin export sessions 2; .export outfile myoutputfile.txt RECORD MODE FORMAT TEXT; select * from databse.table where date_column ge cast ('01/01/2010' as date format 'dd/mm/yyyy') and key_column eq 999999999999 ; .end export; .logoff;
The query runs fine in sql assistant producing a readable text file. When I run fexp.exe the output file has a lot of unreadable rubbish in it.
I was hoping for something simpler. The table has hundreds of rows. Not only do I have to cast each column individually depending upon it's type and desired format, but I also have to include coalesce functions as at least one null value renders the entire concatenation null.
Is there anything simpler? SQL Assistant can export a Teradata table with a simple query though slowly. I was hoping Fast Export would do the same but faster.
I should have said the table has hundreds of columns rather than rows. And there will be many more tables (with millions of rows) like this.
I would need to write a SAS program to generate Fast Export scripts including bespoke cast clauses and coalesce functions for every column as hand writing them would be time consuming and error prone.
I thought it would be prudent to check if I was missing something like a particuliar option on the export statement or something else before I launched into writing/testing/applying yet another code generation script. I get paid for the keystrokes I don't make.
Thanks for looking at my problem but it sounds like Fast Export can't produce a simple readable flat file without coding a cast and coalesce clause for every column in the tables I need to export.
It's clear as a day: you are not willing to use an effective export tool (multiple sessions, support environment, restart, parallel, INMOD/OUTMOD...) because you get paid for the keystrokes you don't make.
On the other hand: hundreds of columns? What an interesting design you have over there!
And tables with millions of rows! Surely the regulars here barely have seen tables like these! Data Warehousing... what does it mean anyway?