Archives of the TeradataForum

Message Posted: Fri, 01 Nov 2002 @ 15:26:15 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Help in fast Export script

From:		Tressler, Dara C.

-----Original Message-----
From: Reddy, Ponnolu

> Question 1: What format do you wish to have the output file (fixed length text, varying length text, Teradata fastload[binary])?

I think, if we specify FORMAT as TEXT, this is valid only for char type..

Text files can serve as INPUT in Multiload and Fastload for any field type even though it is CHAR as the utilities do a conversion on the way in.

what will happen, if we have data in DECIMAL format.....

The DECIMAL exported data can be output as TEXT and used to load into a DECIMAL FIELD.

You just must specify the conversion on the Multi/Fastload.

assume that, in my table I have following columns

name char(20)
phone decimal(12,0) FORMAT '999-999-9999'
location char(10)

How to pull this type of data using Fastexport?

SAMPLE:
/**********************************************************************/
    .LOGTABLE I06_DailyItemTotals_FLOG;
    .RUN FILE ..\secure\loginssa.sql;

    .SYSTEM 'DEL  C:\Teradata\Scripts1025\emp9\emp9.dat';

    .BEGIN EXPORT SESSIONS 20 5;
    .EXPORT OUTFILE C:\Teradata\Scripts1025\emp9\emp9.dat
     MODE RECORD
     FORMAT TEXT;

    select CAST (Name as CHAR(20)),
           CAST ((Phone (FORMAT '999-999-9999')) as CHAR(12)),
           CAST (Location as CHAR(10))
    from Emp9;

    .END EXPORT ;

    .logoff;
    .quit ;
/**********************************************************************/

The above code will export to the file c:\Teradata\Scripts1025\emp9\emp9.dat ALL of the data in table Emp9. It is a fixed length format.

Does fast export allows us to import data also? I got following sample script from Teradata manual. I just tried that one........

It does apparently, but I don't think this is the most efficient way to get data into the database. I would recommend Multiload or Fastload as these were built for just that purpose.

For instance, The following Multiload sample code will load the file just created.

SAMPLE:
/**********************************************************************/
    .LOGTABLE Logtable_EMP9;

    .RUN FILE ..\secure\loginssa.sql;
    .BEGIN IMPORT MLOAD
      TABLES EMP9
          WORKTABLES  WT_EMP9
          ERRORTABLES ET_EMP9
                      UV_EMP9
          ERRLIMIT 50
          AMPCHECK ALL;

    .LAYOUT INPUTLAYOUT;

          .FIELD  IN_Name        * CHAR(20);
          .FIELD  IN_Phone       * CHAR(12);
          .FIELD  IN_LOCATION    * CHAR(10);
          .FILLER                * CHAR(2);  /* needed for Carriage Return/Linefeed */

    .DML LABEL INSERTS
    IGNORE DUPLICATE INSERT ROWS;

    INSERT INTO EMP9
          (
           Name
          ,Phone
          ,Location
          )
     VALUES (
            :IN_Name
           ,:IN_Phone
           ,:IN_Location
            );

    .IMPORT INFILE C:\Teradata\Scripts1025\emp9\emp9.dat
          FORMAT UNFORMAT
          LAYOUT INPUTLAYOUT
          APPLY INSERTS;

    .END MLOAD;
/**********************************************************************/


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference