How to transfar Oracle data to SQL Server

  • Hi,

          Urgently a require has come and I have to transfer some tables and datas from oracle to sql server.But I don't know how to do it.Because I didn't do it before.I have to do this from SQL Server.Can anyone tells me what are the steps should I follow?

    Try to give solution asap.It' very urgent!!!!

    Thanks & Regards

    Niladri


    Thanks & Regards,

    Niladri Kumar Saha

  • Use DTS of SQL Server




    My Blog: http://dineshasanka.spaces.live.com/

  • You have to configure DTS for this.How to configure DTS for this?

    Niladri


    Thanks & Regards,

    Niladri Kumar Saha

  • Hai,

    Check whether this works. In DTS select Microsoft OLE DB provider for oracle

    and enter the Oracle DB name, Password and assign permissions or using ODBC connection

    for ORACLE.

  • Nilardi,

    This should be fairly simple with DTS.

    -create a new Local Package

    -Create a connection to The Oracle DB (Icons on top left)

    -Create a connection to the Sql Server

    -Click transform stat task icon (in lower left box)

    -Select source connection, destination connection

    -double click this task (the line between the connections)

    -select source table on 1st tab

    -select destination table on 2nd tab (or have DTS create it)

    -clean up any transformations on 3rd tab

    -make new tasks for each table you want to import

    If all is good , execute the package.

    Watch for data-types etc

  • If this is a 1 time deal, you don't even need DTS. You go under the Security item in EM, setup a linked server to Oracle. Once that is setup, you can simply run INSERT INTO statements:

    INSERT INTO dbo.SQLServerTable  (column list)

    SELECT (same column list)

    FROM YourLinkName..OracleSchemaOwner.OracleTableName

     

    If you need to pre-create the tables, create them empty with WHERE 0 = 1 clauses

    eg

    SELECT *

    INTO NewSqlTableName

    FROM YourLinkName..OracleSchemaOwner.OracleTableName

    WHERE 0 = 1 -- Create empty table, same structure as Oracle table

     

    (Actually, if you don't mind holding locks for potentially long periods, you can dispense with the WHERE 0 = 1 and create and populate the table in 1 step)

     

  • Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.

    Then using import from SQL server is a simple task.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.

    Then using import from SQL server is a simple task.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Of course, before you can do any of that, you need to install the Oracle Client software (The network portion) and using the Net Configuration Assistant, add a local net service name.

    Then using import from SQL server is a simple task.

    5ilverFox
    Consulting DBA / Developer
    South Africa

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply