Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: How to copy the content of 3000 Oracle tables into SQLServer?

    I have to copy a large (3000) amount of different tables from a Oracle machine into an
    SQLServer machine.
    I am able to do this using a (VB) script.
    I use now several methods:

    1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)

    2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')

    3) Also used OPENROWSET method (similar to 2)

    For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
    If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
    Also, the DTS seems to bulk copy the data directly into the desired database while the
    mentioned methods first fill the tempdb, then the transaction log of the desired database and
    then finally the desired table (need very much extra space on your filesystem).
    The total size of data is about 300GB.

    Can anyone supply me with a simple example how to copy data from an Oracle table into a
    SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
    I read the bcp (which I use for import/export files) and bulk insert commands, but
    I do not understand how to use them in this question.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your best bet is going to be to dump the data out to delimited files from Oracle and bcp them in to SQL Server...

    I would say your going to have at least Date conversion issues...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm sorry. Did you say three THOUSAND tables?
    Might I suggest that now might be a good time to redesign your application? A crappy database is still a crappy database, whether it resides on Oracle or SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    ..the "crappy application" is a quite well known ERP system... (FYI even M$ Navision has over 2000 tables for a single company).
    It is a merge of over 30 different companies that went bankrupt recently which of a part will continue to exist while the other must be kept alive for the time being on another platform for the administrators of the company.

    Indeed, a dump (master.dbo.xp_cmdshell 'bcp ""dbx.dbo.table1"" in ""\\OLS1\ddumps\Od1.dmp"" -q -k -N -U ""sa"" -P""xyz""') proposed by Brett runs a little (30%) faster (unfortunately Unicode is needed), however not the factor 3 that I can reach with a DTS.
    The difference in this speed is (12hours vs 4 hours), that I am able to do an extraction every night (which is a must) or just once a week.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, at least we agree upon its crappiness.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    No other solution(s) in mind?
    I am really desperate.... No experience with DTS scripting etc?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Set your SQL Server database's recovery model option to "Simple" to reduce logging. Use DTS to transfer the data directly from your Oracle source, or use BCP to transfer it from a file.
    Yes, BCP syntax is esoteric, but it is still the fastest method of loading data.
    It seems to me that you are able to transfer the data, but are balking about individually coding all 3000 tables. I don't see any way around that unless you write a batch file to loop through BCPing each file.
    Maybe there is a 3rd party utility that will do this for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Dec 2010
    Posts
    5

    copy tables from oracle 10g to sql server 2008 64 bit

    Hi,

    i too have a huge database with a couple thoudand tables and some other databases in Oracle which are not so big and I have the need to copy nightly ALL data from Oracle 10g to SQL Server 2008 64 bit. Did you find a solution for your problem? Some of the problems I am running into...

    64 bit drivers
    datatype conversions Date and number - big pain in SQL or Oracle depending on which way you look at it.

    Really appreciate any help...

    Sandeep

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sandeep, it is better to start a new thread than to piggy-back on a five year old post.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2010
    Posts
    5

    How to copy the content of 3000 Oracle tables into SQLServer

    ok with me...
    but do u have an answer to my query

  11. #11
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi amukataria,

    I finally did the job in another Oracle database (not in SQL Server).
    This statement runs pretty quick:

    create table SCHEMA.TABLE1 as select * from SCHEMA.TABLE1@link1

    where link1 is a database link to the source database.



    However, if you like to stay with SQL Server the statements:

    1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)
    2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')

    will do the job, but not as quick as Oracle to Oracle. And you still get rid of the data types...

    The tables you can find in Oracle: dba_tables, the fields in dba_tab_columns.
    By reading this data, and translate some datatypes from the dba_tab_colums table into SQL datatypes, you can make a script to create the SQL2008 tables and fill them with Oracle data.

    After this, you can build indices:

    SELECT all_indexes.owner, all_indexes.index_name, all_indexes.index_type,
    all_indexes.table_owner, all_indexes.table_name,
    all_indexes.table_type, all_indexes.uniqueness,
    all_ind_columns.column_name, all_ind_columns.column_position,
    all_ind_columns.descend
    FROM all_indexes, all_ind_columns
    WHERE all_indexes.owner = all_ind_columns.index_owner
    AND all_indexes.index_name = all_ind_columns.index_name
    AND all_indexes.table_owner = all_ind_columns.table_owner
    AND all_indexes.table_name = all_ind_columns.table_name

    Use 64-bit Oracle 10g drivers. These drivers are compatible from Oracle 8 to 11g.

    Success!

  12. #12
    Join Date
    Dec 2010
    Posts
    5

    Oracle to SQL

    hi Wim,

    appreciate your response. I will try to stick with Oracle to Oracle since there will be lesser conflicts that way. but for the record i was able to manage oracle to sql too just like u suggested but it is a pain and it takes along time even to create an empty schema in sql

    live and learn...

    Sandeep

  13. #13
    Join Date
    Dec 2010
    Posts
    5

    sometimes pays to piggy back on done stuff

    it sometimes pays to piggy back on done stuff, right blindman?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, it does not.
    You would have gotten your answer faster if you had started a new thread, and you would have had more of the forum "regulars" responding to you.
    It is considered rude forum etiquette to expect contributors to read through several pages of five year old posts in order to understand your particular problem.
    Also, snarkiness won't earn you many points here either.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2010
    Posts
    5

    you have too much time on your hands

    get a life or better still get a job

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •