My goal here is to read from one directory and write out to another. The database is running version 12.0.1 build 3592. I think I have already addressed any permissions issues as I can successfully read and write into both folders. The source folder table contains 94,000+ entries of various folders, subfolders, and files

I am trying to use a procedure that takes a documentID, reads the path from one of our link tables, pulls the contents of the file with that same path and inserts those contents with a new name into the destination table, writing the file to the folder.

The first issue that I ran into is using "...where file_name = XYZ " to compare the filename to a string (file path) crashes the database. I have not checked to see if this is addressed in an EBF.

So I am instead using the like comparison. When I run the select statement with the like comparison it returns the results instantly:

 select first '87test.jpg'  as file_name, SourceFile.contents as contents  
 from  ClientData as SourceFile 
 where SourceFile.File_Name like  
'Long Client Name\2012\Classification\Long Address\Images\testpic.jpg' ;

But when I use the same select with an insert (either with auto name or specifying the columns) it does not finish. I have let it run for 20 minutes without a result. I added the "first" to the select to try to make it faster, but it does not seem to help.

insert into clientdataland (file_name, contents)
select first '87test.jpg'  as file_name, 
SourceFile.contents as contents  
from  ClientData as SourceFile 
where SourceFile.File_Name like  
'Long Client Name\2012\Classification\Long Address\Images\testpic.jpg' ;

This statement will run quickly, but I assume it is because the file name is not as long as the previous one so the like performs more quickly:

insert into ClientDataLand with auto name
select first 'NewFileName.pdf' as File_Name, Contents from clientdata 
where ClientData.file_name like 'Scan 001.pdf'

As it seems the like with a long file name is causing the issue I would rather use the = , but that crashes the database.

This is how I create the directory servers:

CREATE SERVER ClientDataSRV
CLASS 'DIRECTORY'
USING 'ROOT=D:\Data\ClientData\;SUBDIRS=100;CREATEDIRS=YES';

CREATE EXTERNLOGIN user1 TO ClientDataSRV;

CREATE EXISTING TABLE ClientData AT 'ClientDataSRV;;;.';

CREATE SERVER "ClientDataLND" 
CLASS 'DIRECTORY' 
USING 'ROOT=D:\Data\DestData\;SUBDIRS=100;CREATEDIRS=YES';

CREATE EXTERNLOGIN user1 TO ClientDataLND;

CREATE EXISTING TABLE ClientDataLand AT 'ClientDataLND;;;.';

With all that background, my questions are how do I make the insert (or like comparison if that is the real issue) perform faster, and is the = issue a bug.