Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copying data from one table to another?

Posted on 1999-09-30
13
Medium Priority
?
349 Views
Last Modified: 2010-04-06
Since the completion of my software to 20 of my customers I have found a serious bug.

To fix this bug I have had to add two extra fields into a new table as well as change the code within my program.

My question is how do I swap out he original tables with my customers without losing the last two months of data they have entered.

Can I copy their data across from the original table to the new one I have created.


Thanks in advance



Mike
0
Comment
Question by:mykyl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 17

Expert Comment

by:inthe
ID: 2089023
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
0
 
LVL 15

Expert Comment

by:simonet
ID: 2089036
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
0
 
LVL 15

Expert Comment

by:simonet
ID: 2089040
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mykyl
ID: 2089071
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
0
 
LVL 15

Accepted Solution

by:
simonet earned 1600 total points
ID: 2089143

Let's take this scenario:

You have already deployed your new table with the modified fields, copied the old table to a different folder and are ready to restore the user's data.

Here's the scenario (folder names are fictious... meant only to give you an idea):

Your table is in "c:\YourApp\Table1.db"
Fields:  F_ONE   F_TWO   F_THREE   F_FOUR

The Old Table is in "c:\backup\table1.db"
Fields:  F_ONE     F_TWO

you'll have to create a small utility that maps all info on F_ONE of the old table to F_FOUR of the new table, right?

On a blank form, drop 2 TTable components. Let's call them tbOld and tbNew. Here are their properties as seen on the DFM file:

  object tbOld: TTable
    DatabaseName = 'c:\backup'
    TableName = 'table1.db'
    Left = 80
    Top = 68
  end
  object tbNew: TTable
    DatabaseName = 'c:\YourApp'
    TableName = 'tbNew'
    Left = 120
    Top = 72
  end

Now drop a TBatchMove component on the form and set it up like this:

  object BatchMove1: TBatchMove
    Destination = tbNew
    Mappings.Strings = (
      'F_TWO=F_TWO'
      'F_FOUR=F_ONE')
    Mode = batCopy
    Source = tbOld
    Left = 236
    Top = 104
  end


That will copy all data on the old table to the new table according to the mapping information you setup:

Data in F_TWO is copied to F_TWO
Data in F_ONE is copied to F_FOUR

F_ONE on the destination table is left according to the settings of the MODE property.

Let me know how it went.

yours,

Alex
0
 
LVL 15

Expert Comment

by:simonet
ID: 2089151
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
0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 2089242
Listening...
0
 

Author Comment

by:mykyl
ID: 2089264
Ok I will build the utility tommorrow and give it a try.
I will get back to you.


Cheers


Mike
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2089918
hmm . . .
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2089946
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2089956
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
0
 

Author Comment

by:mykyl
ID: 2092501
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


0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6859537
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.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

670 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