?
Solved

Load DB table from Pipe delimited TStringList (or textfile)

Posted on 2008-10-07
2
Medium Priority
?
595 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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 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