How to Import Oracle Dump Files into SQL Server
When migrating from Oracle to SQL Server or integrating data, you often need to load data from .dmp files into SQL Server.
This article covers two approaches using Open DUMP Viewer for Oracle database.
Approach 1: Direct Import to SQL Server (Recommended)
Open DUMP Viewer for Oracle database includes a direct SQL Server import feature. Load data straight from .dmp files without intermediate CSV or SQL files.
Steps
- Open the .dmp file in Open DUMP Viewer for Oracle database
- Select the target table
- Choose Export → SQL Server
- Enter the SQL Server connection string:
orServer=localhost;Database=mydb;Trusted_Connection=True;Server=192.168.1.100;Database=mydb;User Id=sa;Password=yourpassword; - Confirm the target table name (auto-populated from the source)
- Run
Benefits: No intermediate files. Auto-creates tables. Handles large datasets efficiently
Approach 2: Import via SQL File
Use this when you can't connect directly or want to review the SQL first.
Steps
- Open the .dmp file in Open DUMP Viewer for Oracle database
- Select a table and choose Export → SQL
- Select SQL Server as the target database
- Review the generated .sql file and adjust if needed
- Import using SSMS or sqlcmd:
sqlcmd -S localhost -d mydb -i exported_table.sql
Data Type Mapping
| Oracle | SQL Server | Notes |
|---|---|---|
| NUMBER(p,s) | DECIMAL(p,s) | Precision and scale preserved |
| VARCHAR2(n) | NVARCHAR(n) | Unicode support |
| CHAR(n) | NCHAR(n) | Unicode support |
| DATE | DATETIME2 | Oracle DATE includes time |
| TIMESTAMP | DATETIME2 | Precision preserved |
| CLOB | NVARCHAR(MAX) | Up to 2GB |
| BLOB | VARBINARY(MAX) | Up to 2GB |
| BINARY_FLOAT | REAL | 4 bytes |
| BINARY_DOUBLE | FLOAT | 8 bytes |
Batch Import (Multiple Tables)
For many tables:
- Direct import: Use batch export with SQL Server target to import all tables at once
- Via SQL: Batch export as SQL (SQL Server), then run:
for %%f in (*.sql) do sqlcmd -S localhost -d mydb -i "%%f"
ODBC Import
As an alternative, Open DUMP Viewer for Oracle database's ODBC export can connect through the SQL Server ODBC driver to import data into any table.
Summary
Open DUMP Viewer for Oracle database's direct import feature provides the shortest path from .dmp files to SQL Server — no Oracle environment needed. For review-first workflows, SQL file export is also available.