Export tables from SQL Server to be imported to Oracle 10g

Question

I'm trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle.

I have about 10 tables, varying from 4 columns up to 25. I'm not using any constraints/keys so this should be reasonably straight forward.

Firstly I generated scripts to get the table structure, then modified them to conform to Oracle syntax standards (ie changed the nvarchar to varchar2)

Next I exported the data using SQL Servers export wizard which created a csv flat file. However my main issue is that I can't find a way to force SQL Server to double quote column names. One of my columns contains commas, so unless I can find a method for SQL server to quote column names then I will have trouble when it comes to importing this.

Also, am I going the difficult route, or is there an easier way to do this?

Thanks

EDIT: By quoting I'm refering to quoting the column values in the csv. For example I have a column which contains addresses like

101 High Street, Sometown, Some county, PO5TC053

Without changing it to the following, it would cause issues when loading the CSV

"101 High Street, Sometown, Some county, PO5TC053"

Answer 1

Use the SQLDeveloper migration tools

I think quoting column names in oracle is something you should not use. It causes all sort of problems.

Answer 2

As Robert has said, I'd strongly advise agains quoting column names. The result is that you'd have to quote them not only when importing the data, but also whenever you want to reference that column in a SQL statement - and yes, that probably means in your program code as well. Building SQL statements becomes a total hassle!

From what you're writing, I'm not sure if you are referring to the column names or the data in these columns. (Can SQLServer really have a comma in the column _**name**_? I'd be really surprised if there was a good reason for that!) Quoting the column content should be done for any string-like columns (although I found that other characters usually work better as the need to "escape" quotes becomes another issue). If you're exporting in CSV that should be an option .. but then I'm not familiar with the export wizard.

Another idea for moving the data (depending on the scale of your project) would be to use an ETL/EAI tool. I've been playing around a bit with the Pentaho suite and their Kettle component. It offered a good range of options to move data from one place to another. It may be a bit oversized for a simple transfer, but if it's a big "migration" with the corresponding volume, it may be a good option.

Answer 3

After looking at some options with SQLDeveloper, or to manually try to export/import, I found a utility on SQL Server management studio that gets the desired results, and is easy to use, do the following

1. Goto the source schema on SQL Server
2. Right click > Export data
3. Select source as current schema
4. Select destination as "Oracle OLE provider"
5. Select properties, then add the service name into the first box, then username and password, be sure to click "remember password"
6. Enter query to get desired results to be migrated
7. Enter table name, then click the "Edit" button
8. Alter mappings, change nvarchars to varchar2, and INTEGER to NUMBER
9. Run
10. Repeat process for remaining tables, save as jobs if you need to do this again in the future