Copying Tables from one DB to another DB 
Author Message
 Copying Tables from one DB to another DB

I am working on a project at work, where they would like to be able to
archive data to a large database.  Is there a way in VB to take the data
from tables in one database and append them to the tables in another
database.  The alternative is that we would just append one table to another
in the same database, but would the VB app take a performance hit from
accessing such a large database even though the tables we are accessing are
smaller tables?

The main reason for this is that the company wants to recall up previous
data from months ago, so they want it available, but we don't want to have
the one table get to large, so that queries run fast.

Thanks,

Michael Flagler

--
Posted from ourwebhost.com [64.216.96.9]
via Mailgate.ORG Server - http://www.*-*-*.com/



Mon, 16 Jun 2003 04:24:37 GMT  
 Copying Tables from one DB to another DB
The "brute force" method is to retrieve the records into a recordset, then
create an empty equivalent recordset from the other database, transfer the
data from one recordset to the other, then save the recordset.

We tried getting a recordset from Oracle and saving it to an equivalent
table in Access by closing the recordset and attaching a new Connection, but
that didn't work. Maybe there is some trick like that. If there is, that
would be even faster.

Andy


Quote:
> I am working on a project at work, where they would like to be able to
> archive data to a large database.  Is there a way in VB to take the data
> from tables in one database and append them to the tables in another
> database.  The alternative is that we would just append one table to
another
> in the same database, but would the VB app take a performance hit from
> accessing such a large database even though the tables we are accessing
are
> smaller tables?

> The main reason for this is that the company wants to recall up previous
> data from months ago, so they want it available, but we don't want to have
> the one table get to large, so that queries run fast.

> Thanks,

> Michael Flagler

> --
> Posted from ourwebhost.com [64.216.96.9]
> via Mailgate.ORG Server - http://www.Mailgate.ORG



Mon, 16 Jun 2003 11:54:00 GMT  
 Copying Tables from one DB to another DB
1)Retrieve all of the records into a recordset
2) Loop through the recordset and build a SQL insert statement for each
record concatenating the fields and values onto the insert statement only if
they are not null or substitute a value for them if they are null, that part
is based on your business rules and may be skipped.
3) Before advancing to the next record execute a command object using the
SQL Insert statement that you just built and whatever connection that you
have created to the second database.

I just finished a conversion utility to retrieve records from an Access
database and insert them into an Oracle database.  Works perfectly and even
though it seems as though you are doing a lot of processing it is extremely
fast.  I can remove and write over a 1000 records in less than the blink of
an eye searching on unindexed columns to meet end user criteria.  A status
bar displays the progress of the transfer to the user.


Quote:
> The "brute force" method is to retrieve the records into a recordset, then
> create an empty equivalent recordset from the other database, transfer the
> data from one recordset to the other, then save the recordset.

> We tried getting a recordset from Oracle and saving it to an equivalent
> table in Access by closing the recordset and attaching a new Connection,
but
> that didn't work. Maybe there is some trick like that. If there is, that
> would be even faster.

> Andy



> > I am working on a project at work, where they would like to be able to
> > archive data to a large database.  Is there a way in VB to take the data
> > from tables in one database and append them to the tables in another
> > database.  The alternative is that we would just append one table to
> another
> > in the same database, but would the VB app take a performance hit from
> > accessing such a large database even though the tables we are accessing
> are
> > smaller tables?

> > The main reason for this is that the company wants to recall up previous
> > data from months ago, so they want it available, but we don't want to
have
> > the one table get to large, so that queries run fast.

> > Thanks,

> > Michael Flagler

> > --
> > Posted from ourwebhost.com [64.216.96.9]
> > via Mailgate.ORG Server - http://www.Mailgate.ORG



Tue, 17 Jun 2003 02:52:08 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Copy table from one db to another db with vb5

2. How to copying from one DB table to another DB table

3. Copy Access/Oracle table from one DB to another DB.

4. Copy table schema and data from one DB to another DB

5. Copying data from one DB to another DB

6. Copy Recordset from one Db to a different Db

7. How to copy table from DB to DB?

8. copy runtime a table from db a to db b

9. Copying table from DB to DB in VB

10. Copy a table from one db to another

11. Copy a table from one password protected db to another

12. Exporting Tables From One Access DB to a New One

 

 
Powered by phpBB® Forum Software