I have test data copy from Oracle database to SQL Server. Data will be copied every hour. Data in Oracle is constantly changed and in SQL Server it will be in read-only mode.
That's a pain. The tools which claim to be able to do it require a fat wallet and expertise. This is basically a DB migration project.
A DIY way would be:
- Connect to your Oracle DB with any tool and copy the table structures.
- Find the SQL Server data type equivalent of each column
- Create the same tables in SQL Server with the adequate types
- Dump the data from Oracle into CSV files
- Import the data into your SQL Server
You have to keep in mind that these are two vastly different database management systems. Your best bet is to simplify the structure as much as you can: do away with primary keys, constraints, partitions, schemas, indices etc. If you manage to do that, you can then put a part of it in a script to handle the incremental updates.