1

I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is updated in real-time in the original SQL database.

I have two options:

  1. To pull the data from SQL Server -> Store it -> Push to oracle.

  2. Connect the two databases so as the updates are synchronised in real-time.

Unfortunately, I don't know how to go about this. I have researched on this since a week but haven't found exactly what needs to be done.

Improve this question
7
  • try dba-oracle.com/t_database_link_sql_server_oracle.htm
    – Leo
    Commented Jul 24, 2014 at 4:18
  • The data can never be synchronised in real time, there will always be some kind of latency. How big are the tables, how often are they changed, what is the volume of changes. Is it mainly inserts, updates or a mix of both? You first need to decide on a TRUNCATE/INSERT approach or a CDC (change data capture). You need to decide on a tool to transfer the data. If you are a beginner at this I suggest MS tools, not oracle ones. One MS tool to transfer data like this is `SSIS, though this is not necessarily the best option for real time replication, and is not the only option.
    – Nick.Mc
    Commented Jul 24, 2014 at 4:20
  • oh ,and exactly which version of SQL Server and Oracle?
    – Nick.Mc
    Commented Jul 24, 2014 at 4:22
  • @ElectricLlama The table has 4 columns, and the records are in thousands or more. Its just one table. The tables have the employee in and out details. It updates only, hence the number of records is constant. Some kind of latency should be alright, i guess. SQL server 2012 and oracle 11g.
    – Pribhat
    Commented Jul 24, 2014 at 4:29
  • If there is unlikely to be more than thousands of record then a TRUNCATE/INSERT solution should be fine rather than a more complicated CDC one. Do you want to 'push' the data from SQL Server or pull the data from Oracle? If you want to push from SQL Server you need to install the Oracle driver on the SQL Server.
    – Nick.Mc
    Commented Jul 24, 2014 at 4:31

1 Answer 1

Reset to default
1

this can be done if you are using SQL Server Management Studio. The method is as follows:-

Goto the source schema on SQL Server

Right click > Export data

Select source as current schema

Select destination as "Oracle OLE provider"

Select properties, then add the service name into the first box, then username and password, be sure to click "remember password"

Enter query to get desired results to be migrated

Enter table name, then click the "Edit" button

Alter mappings, change nvarchars to varchar2, and INTEGER to NUMBER

Run

Repeat process for remaining tables, save as jobs if you need to do this again in the future

Improve this answer
11
  • I assume the second time this is run duplicate data will be pushed over. Or does this process automatically clear the target table beforehand? You will also need to install the Oracle driver to do this.
    – Nick.Mc
    Commented Jul 24, 2014 at 4:29
  • you can choose to either append to the existing table or to truncate the table and then add the data.
    – Jay Nirgudkar
    Commented Jul 24, 2014 at 4:30
  • Very handy. What version of SQL Server is that? I can't see the export option in SSMS 2008 R2.
    – Nick.Mc
    Commented Jul 24, 2014 at 4:33
  • I am sorry, Right click the schema -> Tasks -> Export
    – Jay Nirgudkar
    Commented Jul 24, 2014 at 4:38
  • 1
    OK, so you need to be on the server to do this. This will create a SSIS package which you should be able to schedule in a job. You will also need to install the Oracle client on the SQL Server in order to connect to Oracle.
    – Nick.Mc
    Commented Jul 24, 2014 at 4:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.