Post reply

sql to oracle data transfer

  • January 18, 2011 at 12:27 am

    #144288

    Hello friends,

    want to transfer data from sql to oracle having same structure in both database.

    here sql means SQL Server..

    but data will be added every hour in sql database table and want to transfer continuosly.

    whenever the data is available in sql server table it should b transferred immediately.

    Please guide me how can i do that..

  • January 18, 2011 at 12:56 am

    #1274507

    Hi,

    You can try looking after "SQL Server 2005 Import / Export Wizard" in msdn or google.

    Here is an article about it: http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

    Regards,

    Iulian

  • January 18, 2011 at 12:57 am

    #1274509

    after you transferred the data once then you can save the package and schedule it every hour, as you like.

    Iulian

  • January 18, 2011 at 1:14 am

    #1274514

    Thanx Iulian.

    Hi. is it possible using linked server.I come to know this thing from google but not fully described article was there?

  • January 18, 2011 at 5:54 am

    #1274630

    by using linked server you can retrieve\view the data

    suppose u r in MS SQL SERVER ,if u wanted to retrieve the data from MySQL\MS ACCESS .for that we create the linked server for MySQL\MS ACCESS in MS SQL SERVER

    by using openquery u can retrieve the data from that(MySQL\MS ACCESS) database

    [font="Verdana"]SRIHARI(:~[/font]

  • January 24, 2011 at 2:18 am

    #1277080

    With linked server you can build distributed queries.

    In case you need to connect to both SQL and Oracle servers and query them from SSMS on a regular basis you can use linked server.

    If you need to extract data from Oracle only then I would start with "Import and Export Wizard" by right click the destination database > Tasks > Import Data ...

    (from here you have an in intuitive interface to guide you through the process)

    in the end, if you are happy with it, you have the option to save the import steps as a package.

    Then you can schedule or execute the saved package as you need.

    Regards,

    Iulian

  • January 24, 2011 at 2:43 am

    #1277087

    Hi guys,

    Linked server can be used to insert data from SQL SERVER 2005 to Oracle. However, you need to install Oracle's Data Access Components installed on the same system having SQL Server. This will give you a provider in the linked servers in Server Objects Section in SSMS.

    You need to have DSN for the Oracle or you may use a connection string.

    insert into ORCNOTNS..SCHEMA.TableName values ( 11,21,'abc','varchar',51,getdate(),'varchar','varchar','varchar',101,'C',getdate())

    The above statement has ORCNOTNS as the linked server name.

    Please go to this thread

    http://qa.sqlservercentral.com/Forums/Topic574262-146-1.aspx#bm575071

    and you will more details about it.

    Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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