Hi,

I want to load a hold file data into a database (oracle) table. When I use SQLORA, it’s creating a table. In case if the table already exists, it's giving an error message that, "table already exist".

Is it possible to append the table?

Note: It is possible to append, using normal insert statement. But I want to do it directly from hold file.

Please let me know.

Thanks

This message has been edited. Last edited by: <Antony Gubert>,

Antony,

As far as I know, you will need to read (-READ) the data from your hold file into amper variables, then use the amper variables in your SQLORA INSERT statement, as in...


...
-RUN
-READ HOLDFILE &UID.A12 &PGM.A4

-REMOTE BEGIN
SQL SQLORA SET SERVER &SRVID


SQL SQLORA
INSERT INTO PGM_TABLE (USERID, PGM_CD)
VALUES ('&UID', '&PGM')
END

SQL SQLORA
COMMIT
END

-REMOTE END


HTH,
Sean

You could build your SQL Insert statements in the HOLD file and then execute them.

Something like this:

-*== 100 - Prepare SQL Insert statements =======================================

-*-- Designate location for Master Files created with the HOLD command ---
APP HOLDMETA EIDW
-*-- Designate location for data files created with the HOLD command ---
APP HOLDDATA EIDW
-RUN

-SET &LOAD_FILE = 'eidw/crmload1_' || &YYMD || EDIT(&TOD,'99$99$99') || '.fex';

FILEDEF CRMEXTR1 DISK eidw/crmextr1.ftm
FILEDEF CRMLOAD1 DISK &LOAD_FILE
-RUN

TABLE FILE CRMEXTR1
PRINT
COMPUTE INSERT_TEXT/A40 =
'INSERT INTO dbo.Transaction_Summary (';

COMPUTE COLUMN_NAMES/A380 =
'REGION, DEALER_CODE, REP_CODE, CON_GUID, FUND_NUMBER, AS_AT_DT, SALES, ASSETS)';

COMPUTE VALUES_TEXT/A10 = 'VALUES (';

COMPUTE RG_TEXT/A60 = '''' || REGION || ''', ';
COMPUTE DC_TEXT/A10 = '''' || DEALER_CODE || ''', ';
COMPUTE RC_TEXT/A10 = '''' || REP_CODE || ''', ';
COMPUTE CG_TEXT/A60 = '''' || CON_GUID || ''', ';
COMPUTE FU_TEXT/A10 = '''' || FUND_NUMBER || ''', ';
COMPUTE AS_TEXT/A40 = '''' || AS_AT_DT || ''', ';

SALES
COMPUTE SALES_TEXT/A2 = ', ';
ASSETS

COMPUTE END_TEXT/A2 = ');';

ON TABLE HOLD AS CRMLOAD1
END
-RUN

-*== 200 - Execute SQL Insert statements to load Transaction_Summary table =====

SQL
-INCLUDE &LOAD_FILE
END
-RUN

Note: This is cobbled-together code - it may not work the first time around.

Cheers,

Francis.