1

I am currently attempting to transfer a bunch of data from a table in a Sybase database to a table in an oracle database. We are talking a couple hundred thousand records. What would generally be the best practice to do this? Any database software that can help with this? Any suggestions are welcome.

Sigma
  • 33
  • 3

1 Answers1

1

Presumably the data is unloaded in some flat file format, such as CSV or some form of delimited format.

In Oracle, create an EXTERNAL TABLE, which basically maps a flat file to look like a table. The you can simply do a SQL insert

insert into <table> select * from <external table>
BobC
  • 4,208
  • 1
  • 12
  • 15
  • Yes I could dump it into a CSV that isn't an issue. I will have to research this method as I never did this. So you are saying essentially that the external table will be able to query the CSV? Is this now creating a temporary table in ORACLE with that data or simply making it accessible so I can use it to update other tables? – Sigma Jan 10 '17 at 19:51
  • @Sigma. The external table reads the CSV file directly. So you can query it just like any other table. You cannot perform DML on it. It is not a temporary table. – BobC Jan 10 '17 at 19:54
  • Great. This sounds like what I need. Thanks for the help. – Sigma Jan 10 '17 at 19:58
  • 1
    @Sigma - the [bcp utility](http://stackoverflow.com/questions/16931683/how-to-backup-and-restore-sybase-database-tables-using-command-line/16940661#16940661) is the best method to extract large amounts of data from an SAP/Sybase database. You can control the formatting of the export to create a CSV or other delimited data format. – Mike Gardner Jan 11 '17 at 17:17