Need some assistance in determining the best method of loading a DB table.
Currently I have a table called tbExportDocument which has roughly 30 fields in it as it is shared by 3 different scanner platforms (each platform uses most fields, but have fields that are unique to them).
What I'm doing is looping through a list of images and grabbing all kinds of data about them (each image ends up being a record in the table). After processing each image I do an insert into the tbExportDocument table for that image and continue on to the next image.
I was thinking that maybe I could speed up the processing dramatically by storing the values that I would normally pass as parameters to the InsertRecord stored procedure into a TStringlist (each image being on a separate line with the data delimited with a Pipe). After the loop of images is complete, I want to do ONE insert call which inserts the whole thing at the end (instead of 300+ inserts). There would be a HeaderLine that would give me the fieldname mapping for the piped file.
I looked at doing a Bulk Insert, but like I mentioned the data captured and stored for one scanner platform is different than the other 2 scanner platforms that use the same table.
Is there a way to do a Bulk insert to only the fields required by whatever scanner platform I'm working with currently? The table I'm inserting into also has an Identity Seed (ExportDocumentID). I'm not familar with FormatFile that I've seen mentioned in other threads, so if we need to use that I'd need a brief overview of that.
Also, can this be done from a TStringList or would I have to save the contents to a text file (easy enough to do)?
I'll try and put a watered down version of the table here to show an example of what I'm needing.
tbExportDocument:
ExportDocumentID ImgSize IBMLBatch Camera TransSequence DatFileName ScannerType
--------------------- --------- ------------ --------- ------------------ -------------- ----------------
1 2000 12345 1 IBML
2 2200 1 1 OPEX
3 2200 1 1.DAT KODAK
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
--
As you can see each scanner platform uses some of the same fields, but also has fields unique to itself
Example of what my TStringList (or textfile) will look like for each (with one header line with fieldnames)
IBML Example:
ImgSize|IBMLBatch|Camera|S
cannerType
2000|12345|1|IBML
OPEX Example:
ImgSize|Camera|TransSequen
ce|Scanner
Type
2200|1|1|OPEX
KODAK Example:
ImgSize|Camera|DatFileName
|ScannerTy
pe
2200|1|1.DAT|KODAK
Start Free Trial