Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: There has to be a better way to get the data

    I'm no stranger to porting data to SQL Server from Oracle and the other way around. However, there is one thing I really, really hate. If I use DTS and select the tables from the source (Oracle) and it already exists in the destination (SQL) it is not "smart" enough to know they are one and the same so it wants to create the table.

    I've tried to change the object owners to the same user name as what it is in Oracle hoping that I could get around the DBO naming issue (issue may only be in my head) perhaps and thinking the names would match up and I wouldn't have to hit the edit button and select the table on the SQL side so it would "see" that the table exist.

    I have to move 500 plus tables and I don't want to do what I described above 500 plus times. Any hints or tips?
    Last edited by DBA-ONE; 07-24-07 at 19:39.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Edit that package and on the transformation from Oracle to SQL, choose to append the data to existing tabels under the data transformations.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I don't want to do this 400 times!

  4. #4
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    During the time between your first post and second post you seem to have lost 100 tables? is this a problem? :P :P

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DBA-ONE
    However, there is one thing I really, really hate. If I use DTS
    Me too....I hate DTS as well
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Quote Originally Posted by SQLSlammer
    During the time between your first post and second post you seem to have lost 100 tables? is this a problem? :P :P
    Who is counting? Man, this is a pain.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummmm..why don't you generate code from the catalog

    Maybe you can be a little more clear about what you are trying to do?

    Just trying to port Oracle into sql server?

    Why not just create a linked server?
    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.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    could you generate a set of bat files that call SQL*Loader to export the data (can SQL*Loader export? I know it can import, not sure about the other way. i'm not a frequent oracle user obviously) and then call bcp.exe to import?

    You could generate the scripts by querying the system catalogs to output the appropriate bat file. then just copy/paste the result, inspect for correctness, and run.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No SQLLoader does not export amazingly enough..or at least it didn't

    Here's a link that shows you how to export from Oracle..you could probably use the catalog to generate most of the code..this is fixed width, which is harder than comma deleimeted

    http://weblogs.sqlteam.com/brettk/ar...5/05/4898.aspx
    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.

Posting Permissions

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