Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: Automating the importation of an Oracle table into Sql Server 2000

    Hi guys,

    I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue.....

    I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up????

    Thanks in Advance all............

    'Wale

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did 9i ever come out with an export utility?

    Your best bet is to schedule the Oracle export, TRUNCATE the SQL Server table, then do a bcp load or BULK INSERT....

    or you can set up a linked server in sql server and do a delete and an insert from the oracle table...

    The latter will incur more over head....but will appear to be easier....
    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
    Mar 2002
    Posts
    162
    problem is that the ORACLE database is not in my control. So I can't just schedule an export.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I wonder if you can bcp out data from a linked server....

    look into sp_addlinkedserver

    You must authority to that box, right?
    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.

  5. #5
    Join Date
    Mar 2002
    Posts
    162
    yeah I do have authority on the SQL Server box. i'll look into it.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You'll have to look into sp_addlinkedsrvlogin 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.

Posting Permissions

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