Bulk Record Copy Using ODBC Between Data Sources
Tim Meyer -- tmeyer@southeast.net
Wednesday, September 11, 1996
Environment: MSVC 4.2 Windows 95
I am writing an application that runs an ad hoc SQL query against an
ODBC datasource and than places the results into a table in another
ODBC datasource. The results table is created dynamically when the
query is launched. I could generate an INSERT for each row of data
but this is very slooowww. I noticed that one of the samples that
comes with MSVC 4.2 (DBFETCH) has a CBulkRecordsetMod class that has
a RowsetAdd method. Does anyone have any ideas or samples on how to
implement this? One important thing I should mention is that the ODBC
data source you query against can be different from the ODBC data source
you place the results in.
Frank McGeough -- fm@synchrologic.com
Thursday, September 12, 1996
[Mini-digest: 2 responses]
At 09:53 AM 9/11/96 -0400, you wrote:
>Environment: MSVC 4.2 Windows 95
>
>I am writing an application that runs an ad hoc SQL query against an
>ODBC datasource and than places the results into a table in another
>ODBC datasource. The results table is created dynamically when the
>query is launched. I could generate an INSERT for each row of data
>but this is very slooowww. I noticed that one of the samples that
>comes with MSVC 4.2 (DBFETCH) has a CBulkRecordsetMod class that has
>a RowsetAdd method. Does anyone have any ideas or samples on how to
>implement this? One important thing I should mention is that the ODBC
>data source you query against can be different from the ODBC data source
>you place the results in.
There are 2 approaches to this problem (if you insist or have to
do this yourself)
1) If you want to use ODBC for the complete solution then you should
form your insert statement with substitution parameters, SQLPrepare,
SQLBindParameters once and then go into a loop copying data values
(or NULL) into the parameters that you've set up and calling
SQLExecute.
2) Dump the results that you are getting into a load file and run
the database vendors load utility on the load file. This is generally
going to process the results much faster than the call level interface.
There is generally no error handling & bad restartability with things
like this but hey, it goes 10x faster so if an exception occurs just
start the whole thing over.
Another comment is that depending on the db that you are using you
may be able to use the db vendors call level library which may or
may not be faster than their ODBC interface. [insert shameless
endorsement here] That's why I've used DBTools.h++ from Rogue
Wave, it allows me to use ODBC or the underlying DB vendors
call level library --- so I can swap things out if I have to.
Although it's a good class library, like any other class library
it may be more trouble than it's worth for particular problems
but from the general description I would say that you could use it.
[insert futher shamless endorsement here]You may want to look at
some of the data extraction,cleaning,loading tools like Data Junction
that already performs this type of task. Good luck.
------------------------------
Frank McGeough
e fm@synchrologic.com
v 404.876.3209 f 404.876.3809
-----From: "Steve Barta"
You did not state what kind of database you were attaching to
...but, assuming it was Sybase you can issue a insert-select
statement directly to have the data selected from one database(tables,rows)
inserted directly into another one(tables,rows) and avoid the data traffic
to your
PC and back. You might want to explore the capabilities/configuration
of the database you are talking to. It would be a big win for you if you
could construct your query such that the database did all the work.
| Вернуться в корень Архива
|