How to Import Oracle .DMP file into SQL Server?

sql-server oracle import dmp
jams picture jams · Sep 19, 2011 · Viewed 31.8k times · Source

I have a .dmp file (oracle data) and I have to import this file into SQL Server 2008 R2. I tried google but get no clear solution. Oracle is on other machine and SQL Server is on other machine. This .DMP file has only tables and data only nothing else.
Any body has any idea?

Answer

Justin Cave picture Justin Cave · Sep 19, 2011

You can't get there from here. The files that the Oracle export utility (classic or DataPump) generate (which, by convention, frequently use the DMP extension) are proprietary binary files. They can only be consumed by the Oracle import utility (classic or DataPump) which will only allow you to load the data into another Oracle database.

You could load the DMP file into a new Oracle database but then you'll still need to move the data from Oracle to SQL Server. It may well be easier to ignore the DMP file and pull directly from the original Oracle database. There are a variety of tools that can be used to move data from an Oracle database to a SQL Server database. If you want SQL Server to control the process, you could SQL Server Integration Services (SSIS). You could also create a linked server in SQL Server that references the Oracle database and write queries against the Oracle database via that connection. If you wanted Oracle to push the data, you could also use the Oracle Transparent Gateway with Heterogeneous Services to create a database link from Oracle to SQL Server and issue SQL against the remote SQL Server database.

There is a nice StackOverflow thread on moving data from Oracle to SQL Server. The SSIS logic is extremely similar if you're pulling from Oracle to SQL Server or pushing from SQL Server to Oracle.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More