Copying data from one table to another?

Hi
you can add a field using an sql statement like :
 
  with Query1 do
    begin
    close;
    DataBaseName := table.DataBaseName;
    sql.Clear;
    sql.add('ALTER TABLE ' + YourTableName + ' ADD ' + FieldName + ' INTEGER');
    ExecSQL;
    end;

then requires no new table..
an ALTER TABLE statement is just one of a few ways to add fields to or delete fields from an existing table. It happens to be the easiest. It is documented in the local SQL help, along with a source code example. Another way to add or delete fields is with the BDE API function DbiDoRestructure (if you are only dealing with local table types). BDE API functions are documented in the online help file BDE32.HLP, found in the main BDE directory.

Regards Barry

There are several ways to do it, depending on how you want to do it:

1) Using DataPump:

DataPump in a tool that comes with Delphi C/S (or Delphi Enterprise) and that will let you move data from one table to another. User (which can be you) intervention is necessary and it is a separate tool.

2) Using TBAtchMove

By using TBatchMove you can recreate the tables
 (or simply move data back and forth) programatically. This would be a 2 step process:

2a) you will need to create the destination table in the table format you want. You can, optionally, let TBachMove create the destination table for you, if it doesn't have to contain any data.

2b) now, using the TBAtchMove component, set the destination table and source table properties.

2c) now map the fields from the source table to the destination table accordingly. Leave the Mappings blank is the destination table's field structure is identical to the one of the source table

2d) Set the batchmove's mode : for this, I suggest you read the help files, for it can be much more detailed than me.

2e) call TBatchMove.Execute

Now you have a copy of the source table. Do whatever changes you need. When (and if) you need to restore the data to the original table, just use TBatchMove again.

3) If you're talking about a file-based table, you can simply copy the files to a different directory or rename them.

Let me know if this is the info you're look for. Feel free to ask for more details.

yours,

Alex

An addition to my comment:

if both tables (destination and source) already exist, the best option is to se TBatchMove with the Mode bmAppend (or bmCopy)


yours,

Alex

Sorry I forgot to mention I am using Delphi3 Standard but also have Delphi4 pro installed so therefore I won'thave datapump.

Thanks


Mike

I will need to make a utility to automatically swap out the tables at the user end.
Imagine the database involves files called harry.db and harry.mb.
This has two fields called 'one' and 'two'. I have now added two extra fields called 'three' and 'four'.
My customers all have the original one, two but need one > four. I need to replace harry.db and harry.mb with my version  (one > four) without losing any info that they have already entered.
So in reality I need to write a utility to add to extra fields rather than copy info.

Is this correct?

(Looks like loads of reading.)

Cheers

By the way: to make the TBatchMove component execute the actual transfer of data, do this on a button's OnClick event:

BAtchMove1.Execute;

Let me know if you need a small sample application.

Yours,

Alex

Listening...

Ok I will build the utility tommorrow and give it a try.
I will get back to you.


Cheers


Mike

hmm . . .

hi all,

what about a query,

it recommends that both tables exists

sample (localSQL)
insert into "to" (A,B) select A,B from "from"

syntax (localSQL)
insert into destinationtablename (listofdestinationfields) select listofsourcefields from sourcetablename

to fire the query

procedure TForm1.Button1Click(Sender: TObject);
begin
  query1.execsql;
end;

meikl

appendix,

by the
listofdestinationfields
and
listofsourcefields
you can decide which field form the source is to insert in which field from the destination

sample

insert into "to" (A,D) select A,B from "from"

Field B from source will inserted in field D from destination

remark the fields should be have the same typ

meikl

I got an answer from a newsgroup which I have posted here in case anyone else has the same problem.

It worked perfectly.

I will still give the points to Simonet as he was the first to come up with an answer for me.

THANKS

MIKE

If you can pick up a copy of RxLib, I have a solution:

Drop 2 TTables on a form.

Use the fields editor to populate one with your old data structure.  Call
this one tblSource
Use the fields editor to populate the other with your new data structure.
Call this one tblDestination.
Drop a Rx MemoryData on the form.  Call this mdHolder
Borrow the structure of the first table (tblSource)

Code follows:

procedure TForm1.FixTable;
begin
  mdHolder.Open;

  tblSource.Open;
  mdHolder.LoadFromDataSet(tblSource, 0, lmAppend);
  tblSource.Close;

  tblDestination.CreateTable;
  ... (add indexes here)

  tblDestination.Open;
  mdHolder.SaveToDataSet(tblDestination, 0);
  tblDestination.Close;
end;

Test it, of course, but I've done something similar.  Haven't tried it with
AutoInc fields, but it should work nicely.  LoadFrom/SaveTo acts like batch
move.

John

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange