best way to fill data

[earlrainer]

Hi,

I have two databases ,both with different table structure.
I have to move data from about 5 tables from database A to database B.
along the way ,I have to extract data from database A, put it in correct format for database B and then put the data into database B.
what will the fastest method to do that..batch updates,text files etc.
please give your ideas and suggestions.

(both databases are sybase..but I think whether its sybase or orale etc doesnt matter)

 

[GeppoDarkson]

Using Delphi:

While Not TabSrc.Eof Do
Begin
TabDest.Append;
TabDest.FieldByName('Field_X').asString := TabSrc.FieldByName('Field_Y').asString;
(...)
TabSrc.Next;
End;
----------------------------
Using ISQL:

Select TabSrc.Field_x,(...)
From TabSrc;
***
Output To 'C:\TabSrc.txt' Format ASCII
***
Input into TabDest From 'C:\TabSrc.txt' Format ASCII

Obviously the Select should meet the fields of TabDest.

Ciao,
GeppoDarkson.

 

[VintageWine]

You can do the following in SQL Server, Sybase is a close cousin. It requires both databases on the same server.

INSERT INTO database1..table1
(field1,
field2,
field3)
SELECT
field1 * 100,
field29,
field3
FROM
database2..table1

If this is practical I think it will be fastest bar none.

Also check out SET IDENTITY_INSERT

have fun
Simon

 

[MozNZ]

It depends very much on the volume of data. If it's only a few MB, it'll be quicker to write the simple Delphi code GeppoDarkson describes (total time taken will be lower). If it's several GB, one of the dump methods will probably be faster. If you can use VintageWine's solution that would be faster than either, once you have the details worked out.

Assuming more than 100MB of data, I'd check out the INSERT/SELECT option, then start coding the dump if I couldn't work it out.

Data point: inserting about 1GB of text file data into MS-SQL using the Delphi approach took about 15 minutes to run when I did that once. So you probably are not looking at an overnight execution regardless of method.