Solved

Load DB table from Pipe delimited TStringList (or textfile)

Posted on 2008-10-07
2
559 Views
Last Modified: 2008-10-08
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|ScannerType
2000|12345|1|IBML

OPEX Example:
ImgSize|Camera|TransSequence|ScannerType
2200|1|1|OPEX

KODAK Example:
ImgSize|Camera|DatFileName|ScannerType
2200|1|1.DAT|KODAK
0
Comment
Question by:jasonkrueger
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22666614
you could do it with batch inserts if you would have 3 distinct files - one for each scanner
you can bulk insert records with only some of the columns of the tables, but all the records in the file must have the same columns (structure)
0
 

Author Comment

by:jasonkrueger
ID: 22671261
I actually solved this late last night saving the TStringList to  a text file and I created a view for each scanner platform. I do the bulk insert into the view which only contained the fields that I needed from the text file .

I'll give you the points since you offered your time to answer.

Thanks,
Jason
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question