Replicate between two ODBC SQL BE DB's - what to download records into?

[mdlueck]

Greetings,

Given an environment of two separate / different SQL BE DB's able to be connected to by ODBC connection, what other options than using a desktop database file as temporary storage exist to download records from the one SQL BE DB, to then use as the source of a bulk INSERT into the target SQL BE DB?

I would like to leave the download from the source SQL BE DB as a INSERT / SELECT SQL query which executes very fast and does a data dump... yet still want it dumped to an RDBMS type target so that when it comes time to publish to the target SQL BE DB, I need not spend precious processing time having to parse the data... such as dumping to a flat file.

The SQL BE DB's are different products, so their export / import formats are a different proprietary type... and like I already stated I only have ODBC connections to both available to utilize.

 

[Galaxiom]

I assume you need to do some complex processing before the insert or you would just link the tables in both databases and run an insert query directly on them.

Why the aversion to using a temporary local database?

 

[mdlueck]

just link the tables in both databases and run an insert query directly on them.

That might work if there is some sort of guarantee that the master DB has unique enough data to meet the requirements of the ultimate target database. I will definitely keep that in mind.

Checking I understood your take on it... use two linked table objects - one to each DB, then use a DAO.QueryDef object to perform the INSERT / SELECT query upon the two linked table objects?

That was not guaranteed last time, thus I had to download to a temp table, then define a SELECT query with the correct ORDER BY to guarantee that the best record for a particular suppose-to-be-unique-ID always gets selected first, then execute an INSERT SP which itself does a SELECT by unique ID first, if found then silently ends, else does the actual INSERT.

This will be different data for a different department, so no clue what integrity rules will be desirable.

Why the aversion to using a temporary local database?

I would prefer to eliminate using Access entirely... just two SQL ODBC connections, but then what to do about some sort of temporary local database is the question... ODBC drivers by themselves do not magically download records into any sort of local database table. Thus the point which raised the question. "How else, besides using a desktop database product, is it possible to download / upload data between two SQL RDBMS's?"

 

[Galaxiom]

That might work if there is some sort of guarantee that the master DB has unique enough data to meet the requirements of the ultimate target database. I will definitely keep that in mind.

Ah. More to the requirement than previously intimated.

It still should be possible to create a query to handle this but it could well become a very convoluted query depending on how complex the processing would be.

However, queries with heterogeneous joins are very slow.

I would prefer to eliminate using Access entirely... just , is it possible to download / upload data between two SQL RDBMS's?"

What environment are you coding in?