Web Development

Ask something
Discussion Locked

Copy data using SQL

0 helpful
12 replies

by dog the walker

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

12 replies

by john_wills

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

by dog the walker

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?

by john_wills

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

by john_wills

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.

by john_wills

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.

by dog the walker

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.)

by rolie

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.

by dog the walker

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?

by rrv

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

This discussion is closed to new replies.