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

Jimmy picture Jimmy · Nov 13, 2009 · Viewed 23.2k times · Source

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

Jimmy picture Jimmy · Nov 13, 2009

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