Unanswered: There has to be a better way to get the data
I'm no stranger to porting data to SQL Server from Oracle and the other way around. However, there is one thing I really, really hate. If I use DTS and select the tables from the source (Oracle) and it already exists in the destination (SQL) it is not "smart" enough to know they are one and the same so it wants to create the table.
I've tried to change the object owners to the same user name as what it is in Oracle hoping that I could get around the DBO naming issue (issue may only be in my head) perhaps and thinking the names would match up and I wouldn't have to hit the edit button and select the table on the SQL side so it would "see" that the table exist.
I have to move 500 plus tables and I don't want to do what I described above 500 plus times. Any hints or tips?
could you generate a set of bat files that call SQL*Loader to export the data (can SQL*Loader export? I know it can import, not sure about the other way. i'm not a frequent oracle user obviously) and then call bcp.exe to import?
You could generate the scripts by querying the system catalogs to output the appropriate bat file. then just copy/paste the result, inspect for correctness, and run.