How to Migrate from Oracle to SQLServer in PostgreSQL

How do I migrate my Oracle database to SQL Server without data loss?

Move schema objects and data from Oracle to SQL Server using T-SQL, SSMS, and linked-server queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What are the core steps to migrate from Oracle to SQL Server?

Extract Oracle DDL, create equivalent objects in SQL Server, map data types, transfer data with linked-server or SSIS, and validate counts & constraints.

How do I prepare equivalent tables in SQL Server?

Run converted DDL in SSMS. Replace NUMBER with DECIMAL, DATE with DATETIME2, VARCHAR2 with VARCHAR, CLOB with NVARCHAR(MAX).Keep primary keys identical.

Which T-SQL statement copies data row-by-row?

INSERT INTO target SELECT … FROM OPENQUERY(oracle_link,'SELECT …') pulls data over the linked server in one step, preserving column order.

How do I migrate only new rows during a cut-over?

Add a WHERE clause on Oracle timestamp columns in OPENQUERY to fetch rows created after the last copy. Schedule as an Agent job until final switchover.

How can I validate that all rows arrived intact?

Compare COUNT(*), SUM(numeric_column), and MAX(updated_at) between Oracle and SQL Server.Differences indicate missing or truncated data.

When should I use SSIS instead of a linked server?

Use SSIS for billions of rows, complex transformations, or when network latency hampers linked-server performance. SSIS supports batch commits and restartability.

What post-migration tasks are critical?

Rebuild indexes, update statistics, recreate sequences as IDENTITY or SEQUENCE objects, and adjust application connection strings to point at SQL Server.

Why How to Migrate from Oracle to SQLServer in PostgreSQL is important

How to Migrate from Oracle to SQLServer in PostgreSQL Example Usage


--Incremental load of OrderItems added today
DECLARE @last_sync DATETIME2 = '2024-08-30 00:00:00';

INSERT INTO dbo.OrderItems(id, order_id, product_id, quantity)
SELECT id, order_id, product_id, quantity
FROM OPENQUERY(oracle_link,
      'SELECT id, order_id, product_id, quantity
         FROM OrderItems
        WHERE updated_at >= TO_DATE(''2024-08-30'',''YYYY-MM-DD'')');

How to Migrate from Oracle to SQLServer in PostgreSQL Syntax


--1. Create a linked server to Oracle
EXEC sp_addlinkedserver
  @server = 'oracle_link',
  @provider = 'OraOLEDB.Oracle',
  @srvproduct = 'Oracle',
  @datasrc = 'ORCLPDB1';
GO

--2. Copy the Customers table
INSERT INTO dbo.Customers(id, name, email, created_at)
SELECT id, name, email, created_at
FROM OPENQUERY(oracle_link,
      'SELECT id, name, email, created_at FROM Customers');
GO

--3. Copy related Orders in batches
INSERT INTO dbo.Orders(id, customer_id, order_date, total_amount)
SELECT id, customer_id, order_date, total_amount
FROM OPENQUERY(oracle_link,
      'SELECT id, customer_id, order_date, total_amount
         FROM Orders WHERE order_date >= TO_DATE(''2024-01-01'',''YYYY-MM-DD'')');

Common Mistakes

Ignoring data-type mismatches leads to truncation (e.g., Oracle NUMBER(38) into SQL Server INT). Always map NUMBER(38) to DECIMAL(38,0) or BIGINT.
Copying data without disabling SQL Server foreign keys slows inserts dramatically. Disable constraints during bulk loads, then enable and validate.