Re: SQL Server 2000 Migrate to Oracle

>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into
Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.

Hi C Stabri,
on my web site http://www.peternolan.com/downloads.htm#bm006

you will find a suite of free Data Warehousing Utilities. The source code for most of them are also published here.

One of them is the 'Data Transfer Utility' (DTU). I wrote the DTU for doing the kinds of stuff you are referring to.....pushing data around different databases as easily as possible because I can't afford an ETL tool to use as part of my consulting business...

The data transfer utility will move data from one ODBC source to another ODBC target. The freeby executables are windows only but you can write to a unix target via ODBC if that is what you need to do, or create a delimited file that you can transfer to unix.

For your particular problem you can use one of two approaches.

The DTU moves data based on column name or column position. If you want to move by column name you are going to have to put views over the top of your SQL Server tables to present views that 'look like' the views you want to load in Oracle. Or, you can unload the data from SQL Server as delimited files and move it to the Oracle tables based on column number only.

You can migrate the data in a self defining internal format the DTU uses or in some form of delimited file. But if you use delimited files as the target you have to write the load statement for Oracle yourself. It is also possible to take a delimited file and convert it to the self describing internal format and then run it as a set of inserts, thus avoiding having to write the load statements.

If you are doing major volumes let me know as the 'alpha' version is around 2.5 times faster than the 'production' version so you might want the newer 'alpha' version.

A word on data types. Oracle and SQL Server data types are NOT the same even when they have the same name....eg NUMERIC does not mean the same in the two databases.

The DTU unloads data into C character strings and then passes them to the target ODBC DSN specifying the data type of the target. If the target database can convert the character string to the target data type the insert will work. If it cannot, ODBC will give you a cast error. This is the most common problem I have with this utility. To solve cast errors you need to format the data coming out of the source table into something that the target ODBC DSN can understand.

Lastly, there is a manual and a user group for this software....'freebie' means I don't do a whole lot of support for it...;-)

I've not seen anything else out there that is free, easy to use, with source code supplied....so you might want to try DTU.

Best Regards

Peter Nolan Received on Tue Nov 30 2004 - 04:31:20 CST