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 OraDB DUMP Viewer.
Approach 1: Direct Import to SQL Server (Recommended)
OraDB DUMP Viewer 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 OraDB DUMP Viewer
- 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 OraDB DUMP Viewer
- 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, OraDB DUMP Viewer's ODBC export can connect through the SQL Server ODBC driver to import data into any table.
Summary
OraDB DUMP Viewer'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.