0

Possible Duplicate:
Moving Data from SQL Server to Oracle Repeatedly

Is it possible to copy a table from SQL Server to a new table in Oracle?

I am pretty sure that it is not possible, but I would like an expert confirmation and how if it exists.

CREATE TABLE `backup_db.backup_table` 
  SELECT * FROM `live_db.live_table`;

EDIT

To be more clear, I need to copy this table every day from SQL Server to Oracle.

I know that it is not elegant, but I have no other choice.

Community
  • 1
  • 1
sdespont
  • 13,915
  • 9
  • 56
  • 97
  • 1
    No, it does not exist. The migration tool is the right answer. Your design is flawed. – duffymo Oct 10 '12 at 12:31
  • 1
    I know that, but I must live with these two databases. Thanks for your comment – sdespont Oct 10 '12 at 12:33
  • It makes sense if MS SQL Server is a transactional database and Oracle is for data mining and reporting. But in any case you need an ETL tool. There is no simple way to do it like your example. – duffymo Oct 10 '12 at 12:35
  • If you need to push that data from SQL Server to Oracle every day, check out the SQL Server **replication** features. – marc_s Oct 10 '12 at 12:41

2 Answers2

1

Not directly, but you might want to consider setting up a SQL Server Agent job to dump the table to a flat file which is then accessible on a file system to Oracle. Oracle could then access this as an external table.

Robbie Dee
  • 1,939
  • 16
  • 43
1

You can:

  1. Use MS SSIS and transfer data from SQL Server to Oracle using SSIS package and its scheduled execution (MS SQL Server Agent).

  2. Use Oracle Transparent Gateway for MS SQL Server and don't physically pull data from MS SQL Server, but rather select it from MS SQL every time you need it in Oracle. Or, do Oracle insert once per day to transfer data from MS SQL to Oracle still using transparent gateway and linked MS SQL Server:

    TRUNCATE TABLE OracleTable
    INSERT INTO OracleTable SELECT * FROM SQLServerTable@SQLServer
    COMMIT

DimaA6_ABC
  • 578
  • 4
  • 15