Batchmove a range of records between two dates to another table.

[ukusa]

Hi all,

Batchmove using BatCopy, copies across all my records from one table to another even though I have put a range on the source "Date" field. I've used TTable.Batchmove and the
Batchmove component but I can't get the filtered results - what am I doing wrong?

ProgBar.position:=1;
{---- using Datepicker for dates: start and end---}

memstatext:=DatetoStr(stadate.date);
memendtext:=DatetoStr(enddate.date);
With Table2 do
begin
EditRangeStart;
FieldByName('Date').AsString:= memstatext;
EditRangeEnd;
FieldByName('Date').AsString:= memendtext;
Applyrange;
end;

{The Source and destination already specified in component.}

With Batchmove1 Do
begin
Mode:=batCopy;
Execute;
end;
{staext.BatchMove(Table2, batCopy);}
ProgBar.Position:=100;
memused:=staext.RecordCount;
memreject:=(memwhole-memused);

thanks for any help!
Allen

 

[LucieLastic]

I've never used BatchMove. I always do it with pure SQL
like:-

insert into <DestTable> (field1, field2)
select field1, field2 from sourceTable
where datefield between <start> and <end>

Then I would do a delete from the sourcetable if everything is okay.

Sorry, I'm not more helpful.

 

[towerbase]

I would suggest that there is no need to convert your dates into strings first.

  With Table2 do
    begin
      EditRangeStart;
      FieldByName('Date').AsDateTime := stadate.date;
      EditRangeEnd;
      FieldByName('Date').AsDateTime := enddate.date;
      Applyrange;
    end;

 

[svanels]

Maybe this can help, I have downloaded it long time ago:

community.borland.com

Article #25620: An Introduction to the TBatchMove Component in Delphi and C++ Builder

Problem:
What is the TBatchMove component and how do I use it with InterBase (or any other database)?

Solution:
An Introduction to the TBatchMove component
=====================================

1. What is it?
TBatchMove is a VCL component that lets you perform operations on groups of records in a table.
The operations are named with intuitive names that basically tell what they do.
Listed below are the operations or &quot;modes&quot; that the TBatchMove component supports.

- batAppend => appends all records from the source table to the end of the destination
table.

- batAppendUpdate => Appends all records from the source table to the end of the destination
table and updates those records with the same primary index in the
destination table with the same records from the source table.

- batCopy => Copies a table and its records to a new table

- batDelete => Delete all records in the destination table that also appear in the source table.

- batUpdate => Update existing records in the destination table with their counterparts
in the source table.



2. How do I use it?
Listed below are steps to setup and run a TBatchMove component to upsize Paradox data to
InterBase.

a. Put two TTable objects on the form, one for source and one for destination.
1. Click on &quot;Data Access&quot; tab.

2. Click on table icon.

3. Click on form.

4. Press to go to the Object Inspector.

5. Define the Database property for table1 to be the BDE alias that points to the directory
where Paradox tables are located. In this case, we are using the DBDEMOS alias
that is installed by default when you do a full install of Delphi 3.

6. Define the TableName property in object inspector for table1 to be the Paradox table name,
In this example, the table name will be the &quot;orders&quot; sample table that comes with
Delphi. Since we have already specified the location of the tables, you can easily
fill in the TableName property setting by selecting the table from the picklist of the TableName
property.

7. Repeat steps 2.a.1 - 2.a.4 for table2

8. Define the database on table2 to be an alias that points to the InterBase database, in this case
employee.gdb, by selecting the BDE alias that points to the employee database.

9. Define TableName property for table2 to be the table name you want created in
InterBase. For simplicity, call it &quot;orders&quot; as well.

10. Configre the TBatchMove component
a. Put the TBatchMove component on the form.
1. Click on the Mode property's edit box.

2. Click on the BatchMove icon.

3. Click on the form.

b. Define the source and destination for TBatchMove
1. Hit to go to the object inspector.

2. Set the Source property of TBatchMove to be table1.

3. Set the Destination property of TBatchMove to be table2.

d. Specify the mode for TBatchMove
1. Click on the Mode property's edit box.
2. Click on the down arrow that appears to get a picklist of modes to choose from.
3. Click on &quot;batCopy&quot; to select it from the list.

d. Activate TBatchMove
1. Right click on TBatchMove component
2. Click on &quot;Execute&quot; on the pop-up menu that appears.

e. What happens when TBatchMove fails?

You can tell TBatchMove to document what happened when you run a batch move operation
by setting the following properties. They tell Delphi to create a local Paradox table containing
records that were not successfully part of the batchmove operation.

ChangedTablename => Records that were modified by the batch move operation

KeyvioltableName => Records that could not be added because they violated a uniqueness
constraint.

ProblemTableName => Records that could not be added for one reason or another.



Last Modified: 25-OCT-00
Steven van Els
SAvanEls@cq-link.sr

 

[ukusa]

Thank you all for your replies!!!

I have worked out this one.

ProgBar.position:=1;
memstatext:=(stadate.date);
memendtext:=(enddate.date);
With query1 do
begin
close;
unprepare;
SQL.Clear;
SQL.Add('insert into staport');
SQL.Add('select * from caldis');
SQL.Add('where assdate between :memsta and :memend');
ParamByName('memsta').AsDate:=memstatext;
ParamByName('memend').AsDate:=memendtext;
prepare;
execSQL;
end;
ProgBar.Position:=100;
memused:=staext.RecordCount;
memreject:=(memwhole-memused);
with Series1 do
begin
Clear;
Add(memreject,'Rejected');
Add(memused, 'Used');
end;


Thanks again.