Hi,

I got this piece of information about external table from
ibphoenix'
s knowledge base. Hope this will be helpful for you.

Regards

Ben




Assume that the table you wish to export (TABLE1) has two fields
(ID INTEGER,
NAME VARCHAR(10)) and some existing data:

ID NAME
=== ==========
101 Name1
102 Name2
103 Name3

The first step is to add a column to hold a NEWLINE character (2
bytes):

ALTER TABLE TABLE1 ADD NEWLINE CHAR(2);

The next step is to create an external table that will hold the
final exported
data. The fields must correspond to those of the table to be
exported
(including the newly added NEWLINE field), be of type CHAR and
wide enough
to hold the the existing data:

CREATE TABLE T1_EXT EXTERNAL FILE "C:\T1_EXT.TXT"
(ID CHAR(3), NAME CHAR(10), NEWLINE CHAR(2));

Next create a textfile (with an ASCII text editor such as Notepad)
that
contains a single carraige return. In this example assume that I
have named
this file "C:\NL_EXT.TXT". The following CREATE statement will add
the
appropriate information to the system tables for later access:

CREATE TABLE NL_EXT EXTERNAL FILE "C:\NL_EXT.TXT"
(NEWLINE CHAR(2));

INSERTING A CARRIAGE RETURN
First, take a look at what table TABLE1 consists of:

select * from table1

ID NAME NEWLINE
=========== ========== =======
101 name1
102 name2
103 name3

Then you issue the following SQL statement that takes the carriage
return value
in the newline external file (NL_EXT) and updates the NULL newline
column in
the internal table (TABLE1) with it.

update TABLE1 set newline = (select newline from NL_EXT)

Now TABLE1 has:

select * from TABLE1

ID NAME NEWLINE
=========== ========== =======
101 name1
102 name2
103 name3

Notice that the NULL is missing from the NEWLINE column. That is
because it
contains a carriage return. Also, the carriage return value takes
two
characters on Windows platforms, one on UNIX.

EXPORTING TO AN EXTERNAL TABLE/FILE
Now, a simple insert statement to move the data from the internal
table to the
external table/file:

insert into T1_EXT (ID, name, newline) select CAST(ID as
char(4)),
cast(NAME as char(10)), NEWLINE from TABLE1

Note that the integer column in T1_EXT was a char and you had to
use the CAST
operator to move the numeric value. Now, did the data go over?

select * from T1_EXT

ID NAME NEWLINE
====== ========== =======
101 name1
102 name2
103 name3

__________________________________________________________
Great Travel Deals, Airfares, Hotels on
http://r.rediff.com/r?www.journeymart.com/rediff/travel.asp&&sign&&jmart