Copy a TadoDataSet to another and apply changes to database.

Hello,

(Delphi XE4, Sybase Sql Server, TadoDataset)

In a database migration process, i need to transfert Records from a table T1 in Database DB1 to a Table T2 to database DB2.

I m using a TAdoDataSet to hold records from T1 and an other TAdoDataSet to hold a copy to be injected into a empty T2.

The copy is made succefully by doing this :

function CloneRecordset(const Data: _Recordset): _Recordset;
var
  newRec: _Recordset;
  stm: Stream;
begin
  newRec := CoRecordset.Create as _Recordset;
  stm := CoStream.Create;
  Data.Save(stm, adPersistADTG);
  newRec.Open(stm, EmptyParam, CursorTypeEnum(adOpenUnspecified),
    LockTypeEnum(adLockUnspecified), 0);
  Result := newRec;
end;

Open in new window

...
 Destination.Recordset := CloneRecordset(Source.Recordset);

Open in new window


The function copy effectively all record to dataset destination and i can display all record in a DBGrid.

The problem in that all records are not sent to the database.

Doing Destination.UpdateBatch(arAll); do not change anything. the table T2 remains empty.

How to send  prepared records in the "destination" TAdodataset to the database?

This Following  code is functionnal but i m looking for a best way to do this, cause it takes time to insert record by record

Destination.First;
    while not(Destination.Eof) do
      Destination.Delete;

    while not Source.Eof do
    begin
      Destination.insert;
      for i := 0 to Source.Fields.Count - 1 do
      begin
        Destination.FieldByName(Source.Fields[/i].FieldName).Value :=
          Source.Fields[/i].Value;
      end;
      Source.Next;
      Destination.Post;
    end;

Open in new window


Thanks in advance.
NelzoomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lomo74Commented:
I guess the problem resides in using a TADODataSet to pump data to destination.
Excerpt from online help:
TADODataSet is not capable of issuing Data Manipulation Language (DML) SQL statements that do not return result sets (like DELETE, INSERT, and UPDATE)

You can solve by using a TADOCommand, which you set to
INSERT INTO TABLE (col1, col2, col3, ...) VALUES (:param1, :param2, :param3, ...)

Open in new window

and then, for each fetched record:
with cmd do begin
Append;
Parameters.ParamByName('param1').Value := source.Fields[0].Value;
Parameters.ParamByName('param2').Value := source.Fields[1].Value;
...
Post;
end;

Open in new window

or, even simpler, use a TADOTable, point it to the destination table, then for each fetched record do:
with dest do begin
Append;
CopyFields(source);
Post;
end;

Open in new window

0
gskoczylasSenior Software DeveloperCommented:
I do not know the Sybase SQL dialect but I think that you can try to use the TAdoCommand component with SQL statement such like
INSERT INTO T1(fields) SELECT fields FROM T2

Open in new window

0
wilcoxonCommented:
Yes, gskoczylas insert will work in Sybase.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

lomo74Commented:
No, that statement is not going to work. As the author said, the two tables belong to different databases.
0
Geert GOracle dbaCommented:
TAdoDataset is not intelligent enough to understand you want to "merge" when assigning data to a database

you'll have to check each record for actions required
1. doens't exist in the destination > insert a new record
2. exists and is the same > leave alone
3. exists and is different > update the record with the new values
4. untouched records in the destination > delete or leave alone
    the actions in 4. depends if you want to append or match all records
0
NelzoomAuthor Commented:
Hello, Thank you for ure answers.

The insert is working fine as i wrote:

Destination.First;
    while not(Destination.Eof) do
      Destination.Delete;

    while not Source.Eof do
    begin
      Destination.insert;
      for i := 0 to Source.Fields.Count - 1 do
      begin
        Destination.FieldByName(Source.Fields[/i].FieldName).Value :=
          Source.Fields[/i].Value;
      end;
      Source.Next;
      Destination.Post;
    end;

Open in new window


This Insert solution in very time consuming and depends on records numbers.

is there any way to send the "destination" AdoDataSet records to database faster?
0
Geert GOracle dbaCommented:
yes, use the bulk copy/insert tools that come with the database and use them from the server hosting the database

Now you have 2 extra network layers, if not more
from the db server, to the pc, processing, to the dest server and db

otherwise
from the db server to the dest db server.
0
NelzoomAuthor Commented:
Hy Geert_G,

it Seems to be complicated and i don't want to use any external tools or line command from my delphi programm.

thanks to all for ur answers even no clear solution provided

How to give points in this case?

thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
give points according to what helped.
there is a topic about giving points in the help section

if someone really doesn't like how the points were given, they'll object
0
Pierre CorneliusCommented:
I don't have sybase installed so can't test but how about:

INSERT INTO YourTableInDB1 (somefield, somefield2)
LOCATION 'servername.db2' PACKETSIZE 512 ' SELECT somefield, somefield2 FROM YourTableInDB2'

refer to:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00171.1270/html/iqnew/iqnew54.htm
0
NelzoomAuthor Commented:
no solution was suggested to resolve my problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.