CODEWITHSUNDEEP
sqldatabaseoracle-database
mayowa ogundele
mayowa ogundele

Reputation: 463

Copying from Oracle into a SQL Server table

I want to copy the contents of a column in an Oracle database table into another column in a SQL Server database. I have over 3 million rows to transfer. Is there any short way of solving this kind of problem. Not all the Content in the Oracle table will be transferred, because the Oracle table has about 4 million rows

Upvotes: 1

Views: 7841

Answers (2)

knagaev
knagaev

Reputation: 2957

For clarification of the last sentence of Ciarán's answer. I used to do it this way:

  • export data from Oracle table with sqlplus and SPOOL (sample);
  • use bcp utility for fast load into MS SQL (documentation).

Upvotes: 0

Ciarán
Ciarán

Reputation: 3057

Well, assuming you do mean SQL/Server, I would use SQL Server Integration Services (SSIS) this would be fairly easy to do and I've done something like this fairly recently. One major tip is to stage the data in an unindexed table where the Oracle VARCHAR2 columns (if you have them) map to NVARCHAR in SQL/Server, this avoids a data conversion step in SSIS. From the staging table you can put them into their correct destination table and get rid of the staging table after. This is the way I've done it.

You could import the data into an MS/Access database and use the SQL/Server Import/Export wizard. This is quite quick and maybe your best option if you have Ms/Access handy.

You could create a Linked Server in SQL/Server from which you could simply suck the data over into your database. I've had mixed results with this approach. It does work but I've found it can be kinda flaky on occasion. This may be something to do with our infrastructure rather than an inherent weakness of Oracle Linked Servers. You could maybe partition the data logically and pull it into SQL/Server piecemeal if you run into problems. Linked Servers are quite elegant though if you can get them to work. You use OPENQUERY() to get retrieve the data from Oracle, E.g.

Select * Into SqlServerTableName 
  From OpenQuery(Oracle_Linked_Server,'Select * from OracleTable
                                        Where etc');

You could also export the data into a flat (or CSV) file and import that, again SSIS could do this but it would not be as easy. This is kinda prone to formatting and export/import discrepancies

Upvotes: 1

Related Questions