Hi Patrick,
Under the hood, the SYSCS_IMPORT_TABLE procedure prepares and executes
an INSERT INTO ... SELECT * FROM statement using its own custom table
function. So you should get the same good performance from your own
table function as you get from the IMPORT procedure.
Hope this helps,
-Rick
Patrick Meyer wrote:
Thanks for your response. Do you know if extending FlatFileVTI will
work as fast as the built-in IMPORT_TABLE function? Before using CALL
SYSCS_UTIL.SYSCS_IMPORT_TABLE, I had written my own class that would
read a delimited file and use the INSERT INTO statement to populate my
Derby db. However, it took my class 1 hour to import a file but it
only took one minute when I used SYSCS_UTIL.SYSCS_IMPORT_TABLE. Will
extending FlatFileVTI work a quickly as using
SYSCS_UTIL.SYSCS_IMPORT_TABLE?
Thanks
Rick Hillegas wrote:
Hi Patrick,
You can write a table function to help you out here. Your table
function will open your file, throw away the header, and then loop
through the lines in the file. I would recommend extending something
like FlatFileVTI (in Derby's demo directory). You can look at
PropertyFileVTI (in the same directory subtree), which will show you
how to extend FlatFileVTI. Once you declare your table function
(let's call it MyFileReader), you can then use it to populate your
table as follows:
insert into STAFF
select * from table( MyFileReader( 'myfile.del' ) ) s
Hope this helps,
-Rick
Patrick Meyer wrote:
I have a comma delimited file that contains a header in the first
row of the file. The header contains column names. If I manually
create a new file without the header, I can successfully import the
new file with
CALL
SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'STAFF','myfile.del',null,null,null,0);
Creating the new file in my Java application is too time consuming
for it to be a good solution. Is there a way of importing a
delimited file with a header in the first row? Or, a way to import
and ignore the first row of the file being imported?
Thanks
