How to store big table data?

Moehre · Post by **Moehre** » Tue 27 Oct 2009 10:11

Dear sirs!

I try to store table data in a file to transfer it to another database using "SaveToXML" into a memory stream, compress it and store it to a file. This works as long as table data is not too big, but now I had to store more than 1 million rows and so I get out of memory.

Is there any way (cursor or so) to divide the data into several peaces, so that I will not run out of memory?

Here is an example: (ms1 is my memory stream)

qryDaten.Close;
qryDaten.FetchRows := 500;
qryDaten.SQL.Text := 'SELECT * FROM ' + tablename;
qryDaten.Open;

ms1.Clear;
qryDaten.SaveToXML(ms1);
ms1.Position := 0;
Compress1.CompressStreamToArchive(filename,ms1,tablename + '.DAT',coLH5);

Thx in advance
Moehre

Plash · Post by **Plash** » Wed 28 Oct 2009 11:31

You should modify your query:

Code: Select all

SELECT * FROM Table1
WHERE Id BETWEEN :Id1 AND :Id2

Assing values for parameters and execute the query to get a part of data.

Moehre · Post by **Moehre** » Thu 29 Oct 2009 06:44

Hi Plash!

Unfortunately that won't work for me, because the customer can store any table he wants; so I don't know the key fields or their value range to do so.

Is there any way to use the rowid? In Oracle I could select with "ROWNUM < xxx ORDER BY ROWID", but how to do so using MS SQL Server, Access or DB2 ??

Thx
Moehre

Plash · Post by **Plash** » Fri 30 Oct 2009 08:17

Maybe you should try to save an XML to a file and compress this file.