Win XP: Problem with shell scripting in Python

[A.M]

> I dare hardly suggest this, but might it not be better to use Python's
> database functionality to perform the task? The language can access both 
> databases, and you might find it quicker. Then again, if your database 
> experience is limited, you may not ...
>
> regards
>  Steve
> -- 
> Steve Holden       +44 150 684 7255  +1 800 494 3119
> Holden Web LLC/Ltd          http://www.holdenweb.com
> Love me, love my blog  http://holdenweb.blogspot.com
> Recent Ramblings     http://del.icio.us/steve.holden
>

Hi Steven,



Based on my experience, the fastest possible way to import raw data into 
Oracle is SQL*Loader. Similarly, the fastest way to extract raw data from 
SQL server is BCP.



My script transfers 40,000,000 records (actually big records) from sql 
server to oracle in 20 Min. I tried ODBC to do the same work. I turned off 
all record locking and transactions through query hints. The actual program 
was a C# program. After 12 hours, I just stopped the program.



I created DOS batch files to control BCP and SQL*Loader steps. It is faster 
than any fancy GUI tools. Now I am using Python.



I am thinking to add comprehensive logging to the ETL (extract transform 
load) process. All details command line outputs will be stored in database. 
System administrators can query database and watch how the ETL job is 
working.



At this point I have quite challenge with capturing BCP's stdout/stderr 
output to string variables in Python program.



I'll post the final outcome here.



Regards, Alan