Using ADO to bulk move Data

[cmgaviao]

I have an application which downloads data from one database, does some formatting, and moves it into my proprietary database format on SQL server or Oracle.

In the past, I've always downloaded, dumped to Paradox .db tables, formatted, and batch moved into to the server from the Paradox .db tables. The reason for the intermediate step of moving to the Paradox .db tables is that once the data is downloaded, it needs to be portable so that for instance a client running the download portion in finland can download, send on to me and I can import.

I have embarked on creating a new version of this process, and have elected to use ADO so that I can be rid of the BDE (It's been causing me headaches in deployment and also running on Multi-Proc machines). As far as the Download portion goes, this is easy, simply run my query and then the TadoDataset supports TADODataset.SaveToFile and I can either save it as the binary file type or XML. Importing, seems to be a bit more of an Issue, I can use an ADOTable, or Dataset and LoadFromFile, which moves it into the Set, but not into the Server. In the BDE World, I suppose this is where batchmove would come in....

Is the best way to get the data into the server simply to cycle the dataset, executing an Insert statement at each row? Or, is there some sort of shortcut to move the entire dataset that I'm missing?

TIA for any assistance.

 

[cmgaviao]

Anybody....anybody....Bueller....Bueller...?

 

[Karen99]

Sorry can not help you !!!

But maybe you can help me !!

It seems like you have been doing this already.

I have to copy a Paradox table to Sql Server. It works fine with TBatchmove. It copies the table and data. But it does not copy the primary keys and indexes.

Can you copy the primary keys with TBatchmove or must I use something else ?

How did you do it ?

Thank
Karen

 

[VintageWine]

Check out SQL Server Data Transfer Services (DTS) and skip Delphi altogether.

If you really must use Delphi it is a one record at a time job, which is all TBatchMove does.

<<<Is the best way to get the data into the server simply to cycle the dataset, executing an Insert statement at each row?>>>
Yes

A TDataset does not hold index information for the underlying table and so could not replicate it into somewhere else. To replicate indexes you need to interrogate the source database to establish what indexes exist, then write the corresponding data definition SQL statements to recreate the indexes in the target database. Interrogating the source database can be a bit of a game. The BDE and ADO will yield a lot of information if asked correctly. SQL Server has inbuilt stored procedures which give the answers.

Good luck

 

[cmgaviao]

Hi Karen,

If you know the table layout and indexes before hand, I would suggest simply using a Tquery to create the tables and indexes. (This is how I do it.) If you don't know that information, then as VintageWine has pointed out, you can retrieve this info from the BDE TTable componenet.