General discussion

Locked

Copy data using SQL

By dog the walker ·
Here is the situation:

I have two different and separate databases and need to refresh data from a table on the source database to the corresponding table on the destination "dest" database.

Facts:
1. The primary platform is MSSQL 7/2000 (butalso needs to work in Sybase Studio and Oracle). So hopefully some generic SQL will work.

2. Simply clearing the data on the dest and coping from the source is too slow and I cannot have the data cleared completely at any time on the dest.

3.I cannot develop synchronization code because I don't have programmatic control over all of the places need to set a dirty flag, etc on the source data.

4. The dest data never changes or is deleted.

5. Development Language: VB 6

6. The tables and fields are named different in each database.

Are there any programming tips or fancy SQL statements that would make this happen fast?

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Copy data using SQL

by john_wills In reply to Copy data using SQL

Facts 5 & 6 are easy enough to deal with:
DoCmd RunSQL "INSERT INTO DestTable1 SELECT * FROM SourceTable1;"

Collapse -

Copy data using SQL

by dog the walker In reply to Copy data using SQL

Thanks...

How does this work for item 6 if the field names are different? Is there a way to map the corresponding fields in the insert command to match the fields in the source data? Note that the field order is different in each table as well.
What about data this is already in the dest table?

Collapse -

Copy data using SQL

by john_wills In reply to Copy data using SQL

INSERT INTO DestTable(shark,dog,carrot) SELECT tiburon,perro,zanahoria FROM SourceTable;

Collapse -

Copy data using SQL

by john_wills In reply to Copy data using SQL

Rows already in the destination table will not be affected unless there is a key match. By putting a WHERE on the SELECT part of the INSERT statement you can specify which rows from the source table are to be converted into the destination table. When necessary, you can DELETE rows in the destination table using WHERE.

Collapse -

Copy data using SQL

by dog the walker In reply to Copy data using SQL

How about the fact that they are between two different database instances? (On different servers, etc).

Collapse -

Copy data using SQL

by john_wills In reply to Copy data using SQL

To get data from several source databases, link to the relevant tables from the destination database. If a table of the same name is already in your destination database, the linked one will have a slightly longer name, ending in "1" or "2", etc. Tolink, ALT F/Get/Link and the rest should be obvious.

Collapse -

Copy data using SQL

by dog the walker In reply to Copy data using SQL

Thanks for your help, I really appreciate it! I hate to be so negative but will that last answer also work for Oracle and Sybase Studio?

This is a commercial grade application that must be able to work with all three platforms without a lot of setup by the end-user, such as linking tables, etc.

(I should have mentioned this in the question.)

Collapse -

Copy data using SQL

by rolie In reply to Copy data using SQL

I have develop havee same applicaton with VC++ 6. I have no code for you now for VB. but i'll give you a hint of what to use. Together with the MS SQL Server 2000 called BCP(Bulk Copy Progarm) that copy large amount of data form table to table or form database to another database. You can automate the process of this in VB that will supply parameter for the BCP program. You can also explore the SQL command BULK COPY.

Collapse -

Copy data using SQL

by dog the walker In reply to Copy data using SQL

Thanks.

The big problem we have is that the same program needs to work with MS SQL, Sybase Studio and Oracle.

How about if I setup a collection class is VB to read the data from both sides, loop throguh it, then copy over only the changed/new/delected records?

Is there away to make an update statement perform like an insert statement if the record doesn't exist?

Collapse -

Copy data using SQL

by RRV In reply to Copy data using SQL

1) Use Data Transfer service in SQL server and get the data as table from any other database.
2) Use FTP to transfer data from ORACLE to SQL
3) In VB6 connect to Source database and read the table. Connect to Destination table in batchoptimistic mode and client cursor. for each record in source fill destination recordset. Once loop is over issue Updatebatch method of recordset for the destination. This works fine.

ex.
Read Source in SourceRS in readonly mode
Read Destination in DestRS with write access
destRS.Delete adAffectAllChapters 'if required
do while SourceRS.EOF
DestRS!Field1=SourceRS!Appropriate_field1
DestRS!Field2=SourceRS!Appropriate_field2
...
SourceRS.movenext
loop

DestRS.UpdateBatch adAffectAllChapters

if there are too many records, you can program inside the loop to update for every 500 records in the recordset or so. The recordcount in the Destination recordset tells how may records are written.

RV

Back to Web Development Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Software Forums