Update Micros 3700 database table from txt file

[savissimo]

Hello, i have a little problem with updating currency_def table in micros 3700 database. I have an export file which contains obj_num, name, conversion_rate with ';' delimiter between columns. I am using INPUT statement, but as i can see, this statement is used only when the table is empty, so updating table with statements such as INPUT or LOAD TABLE is not possible. Is there another way to update data in table?

Thanks!

 

[pmegan]

What version of 3700 are you using?

 

[savissimo]

I am using version 4.1 with HF3

 

[savissimo]

Small update. I found the solution thanks to the good colleagues of mine. So this is how the sql script looks like:

CONNECT custom IDENTIFIED by custom;
DECLARE LOCAL TEMPORARY TABLE "currency_temp"
(
obj_num NUMERIC (6),
conversion_rate NUMERIC (6)
)
ON COMMIT PRESERVE ROWS;

INPUT (obj_num, conversion_rate) INTO currency_temp
FROM 'd:\\import.txt'
FORMAT ASCII DELIMITED BY ';' ;

UPDATE micros.currency_def
SET micros.currency_def.conversion_rate=currency_temp.conversion_rate
FROM currency_temp
WHERE micros.currency_def.obj_num=currency_temp.obj_num

and this is how the file import.txt looks like:

1;800;
2;1000;