5

I have to migrate from MS SQL Server and now creating a new ORACLE database with the data partially got from my old tables.

For that I need to get the data from dozens of tables as unicode CSV or TXT files, with fields separated by "|".

The bad thing is the default export master of MS SQL Server can export only single table at a time, and it takes a considerable time to configure export options for each table. I started to export them manually but I got an attack of nerves at the 10th table.

Do you know the way how to export ALL the tables into csv or txt from SQL server?

Andrew
  • 321
  • 3
  • 5
  • 11

4 Answers4

3

A more robust solution is to use the Oracle Migration Workbench. This used to be a separate utility from Oracle, but is now included in Oracle SQL Developer . If you and CLOB/BLOB data types then this is the way to go. You should also read the Oracle documentation on how to migrate as the SQL Developer migration interface is not very intuitive.

If you do want to go down the road of exporting all tables to csv you can use BCP or SSIS. There is a very good article on simple-talk on using some t-sql and to generate bcp export commands for all tables in a database. You can also create simple SSIS package to export all the tables to csv by using a flat file destination task.

here's something quick & dirty that will generate the BCP commands for you. Run it in SSMS, the run the output at the command prompt.

            USE AdventureWorks
SELECT 'bcp ' +
            QUOTENAME(DB_NAME())
            + '.'
            +QUOTENAME(SCHEMA_NAME(schema_id))
            + '.'
            +QUOTENAME(name)
            + ' '+'out'+' '
            + name + '.csv -w -t"|" -E -S '
            + @@servername
            + ' -T'
    FROM sys.objects
    WHERE TYPE='u'
        AND is_ms_shipped=0
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
0

If you have access to SQL Management Studio (I believe even the free express version has the feature as well), you can right click on the data base and choose Tasks -> Export Data. As your destination, select the 'Flat File' destination and you can tweak the format (Fixed width, csv, etc).

Coding Gorilla
  • 1,938
  • 12
  • 10
  • 1
    I can't export all my tables this way, only 1 by 1. I have to pass "Tasks -> Export Data -> ... -> Flat file -> settings -> select single table for export" for each table. It's too damn big database, I need a bit faster solution. – Andrew Dec 08 '10 at 19:09
0

There is a tool called Oracle Migration Workbench that you can get from Oracle that is designed for exactly what you want. This tool can take entire schemas including users and migrate them from MS Sql to Oracle.

I have used it heaps of times and it is excellent.

Here is some more info http://download.oracle.com/docs/html/B10259_01/toc.htm

J

James
  • 128
  • 3
  • 15
  • is it compatible with SQL Server 2008? – Andrew Dec 08 '10 at 20:06
  • Good question and it seems no is the answer however there may be workarounds
    http://forums.oracle.com/forums/thread.jspa?threadID=843319&tstart=-1     – James Dec 08 '10 at 21:18
  • My previous comment got garbled -- Good question and it seems no is the answer however there may be workarounds http://forums.oracle.com/forums/thread.jspa?threadID=843319&tstart=-1 Another option might be to downgrade from 2008 to 2005 or at least export as a 2005 db and then import. Here is another article http://www.oracle.com/technetwork/database/migration/sqlserver-095136.html – James Dec 08 '10 at 21:47
  • Thanks for help, James. The script from Nick Kavadias works fine in BCP – Andrew Dec 09 '10 at 20:56
0

You can try dbForge Studio for SQL Server and Oracle for this purpose. Start page, Menu "Database", then Export Data, next choose "CSV" in the wizard and other options for your database.

Den
  • 1