Compare data between two databases: SQL Server to Oracle in 3 steps

#Two engines, one question

The post on comparing two tables in SQL ends where this one starts: the tills write to SQL Server, the ERP keeps its sales in Oracle, and you want to know which stores disagree. EXCEPT and a join need both tables in one place, and a hash only helps if both engines compute the same one.

There are three routes, and they suit different sizes:

The examples use an invented chain of 120 stores and keep one row per store, so you can check every result by eye. Your tables will have thousands of lines per store; the queries don't change.

#The two tables

On SQL Server, the tills' table. These are the same rows as in the SQL tables post:

CREATE TABLE dbo.till_sales (
  store         varchar(10)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  units         int           NOT NULL,
  tax_code      varchar(10)   NULL,
  CONSTRAINT pk_till_sales PRIMARY KEY (store, business_date)
);

INSERT INTO dbo.till_sales VALUES
  ('ST-004', '2026-11-02',  9850.20, 412, 'STD'),
  ('ST-011', '2026-11-02',  7301.40, 288, 'STD'),
  ('ST-014', '2026-11-02', 12400.00, 530, 'STD'),
  ('ST-022', '2026-11-02',  6410.75, 251, 'STD'),
  ('ST-031', '2026-11-02',  8120.00, 344, 'STD'),
  ('ST-037', '2026-11-02',  5214.60, 199, 'STD');

On Oracle, the ERP's table, with the types an Oracle ERP tends to use. Run it in a scratch schema; the queries below call that schema ERP, so change the prefix to yours.

CREATE TABLE ERP_SALES (
  STORE_CODE    CHAR(8)      NOT NULL,
  BUSINESS_DATE DATE         NOT NULL,
  NET_SALES     NUMBER(12,2) NOT NULL,
  UNITS         NUMBER(6)    NOT NULL,
  TAX_CODE      VARCHAR2(10)
);

INSERT INTO ERP_SALES VALUES ('ST-004', DATE '2026-11-02',  9850.20, 412, 'STD');
INSERT INTO ERP_SALES VALUES ('ST-011', DATE '2026-11-02',  7301.40, 288, NULL);
INSERT INTO ERP_SALES VALUES ('ST-014', DATE '2026-11-02', 11920.00, 512, 'STD');
INSERT INTO ERP_SALES VALUES ('ST-031', DATE '2026-11-02',  8119.96, 344, 'STD');
INSERT INTO ERP_SALES VALUES ('ST-037',
  TO_DATE('2026-11-02 06:15', 'YYYY-MM-DD HH24:MI'), 5214.60, 199, 'STD');
INSERT INTO ERP_SALES VALUES ('ST-047', DATE '2026-11-02',  2392.30,  96, 'STD');
COMMIT;

Six rows on each side, so a row count says nothing is wrong. Hidden in there: ST-014 is 480.00 short in the ERP, ST-022 never arrived, ST-047 is in the ERP only, ST-031 is 0.04 out by rounding, ST-011 lost its tax code, and ST-037 was posted again by a rerun at 06:15, which is a trap rather than a difference.

#Step 1: link Oracle to SQL Server

A linked server lets SQL Server send a query to Oracle and read the answer as if it were a table. Microsoft's sp_addlinkedserver page lists the Oracle Provider for OLE DB, OraOLEDB.Oracle, with the Oracle alias as the data source. The provider has to be installed on the machine SQL Server runs on.

In SQL Server Management Studio:

1. In Object Explorer, expand Server Objects, right-click Linked Servers and select New Linked Server.
2. On the General page, type a name in Linked server (ERPORA), select Other data source, and pick Oracle Provider for OLE DB in Provider. If it isn't in the list, the provider isn't installed on that machine.
3. Type Oracle in Product name and the Oracle alias in Data source.
4. On the Security page, under Local server login to remote server login mappings, select Add, choose your login, and give the Oracle user and password.
5. Select OK, then right-click the new linked server and select Test Connection.

Or in T-SQL, which you can keep in source control:

EXECUTE master.dbo.sp_addlinkedserver
    @server     = N'ERPORA',
    @srvproduct = N'Oracle',
    @provider   = N'OraOLEDB.Oracle',
    @datasrc    = N'ERPPDB';

EXECUTE master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'ERPORA',
    @useself     = N'False',
    @locallogin  = N'CORP\recon_job',
    @rmtuser     = N'RECON_READER',
    @rmtpassword = N'********';

-- Remove the mapping that sp_addlinkedserver adds for every local login
EXECUTE master.dbo.sp_droplinkedsrvlogin N'ERPORA', NULL;

EXECUTE master.dbo.sp_testlinkedserver N'ERPORA';

Two security points, both from Microsoft's pages. sp_addlinkedserver adds a default mapping for all local logins, and Microsoft suggests removing it, which is what sp_droplinkedsrvlogin … NULL does. And the Be made using this security context option on the Security page lets any user on the instance reach Oracle as that user. Map named logins instead, and give the Oracle user SELECT on the tables you compare and nothing else.

#Step 2: compare totals per store, counted in Oracle

OPENQUERY sends its text to Oracle exactly as written, so the grouping happens there and only one row per store comes back. A four-part name such as ERPORA..ERP.ERP_SALES can drag every row across the link before SQL Server filters it.

Two limits shape the code, both on Microsoft's OPENQUERY page: the query string can be at most 8 KB, and OPENQUERY does not accept variables. To run it for yesterday rather than a fixed date, build the statement as text and run it with EXEC:

DECLARE @day date = '2026-11-02';

DECLARE @oracle nvarchar(4000) = N'
SELECT TRIM(STORE_CODE)   AS STORE,
       COUNT(*)           AS LINE_COUNT,
       SUM(NET_SALES)     AS NET_TOTAL
FROM ERP.ERP_SALES
WHERE BUSINESS_DATE >= DATE ''' + CONVERT(nchar(10), @day, 23) + N'''
  AND BUSINESS_DATE <  DATE ''' + CONVERT(nchar(10), DATEADD(day, 1, @day), 23) + N'''
GROUP BY TRIM(STORE_CODE)';

CREATE TABLE #erp_day (store varchar(10), line_count int, net_total decimal(12,2));

DECLARE @sql nvarchar(max) = N'
INSERT INTO #erp_day (store, line_count, net_total)
SELECT STORE, LINE_COUNT, NET_TOTAL
FROM OPENQUERY(ERPORA, ''' + REPLACE(@oracle, N'''', N'''''') + N''')';
EXEC (@sql);

WITH t AS (
  SELECT store, COUNT(*) AS line_count, SUM(net_sales) AS net_total
  FROM dbo.till_sales
  WHERE business_date = @day
  GROUP BY store
)
SELECT COALESCE(t.store, e.store)  AS store,
       t.net_total                 AS till_net,
       e.net_total                 AS erp_net,
       t.net_total - e.net_total   AS difference,
       CASE
         WHEN e.store IS NULL THEN 'Missing in ERP'
         WHEN t.store IS NULL THEN 'Missing at tills'
         WHEN t.line_count <> e.line_count THEN 'Line count differs'
         WHEN ABS(t.net_total - e.net_total) > 0.05 THEN 'Net differs'
         WHEN t.net_total <> e.net_total THEN 'Rounding, within 0.05'
         ELSE 'Totals match'
       END AS result
FROM t
FULL OUTER JOIN #erp_day AS e ON e.store = t.store
ORDER BY 1;

The REPLACE doubles every quote in the Oracle text, because it now sits inside a string inside a string. Print @sql the first time and read it before you run it.

It finds ST-014, ST-022, ST-031 and ST-047, and says ST-004, ST-011 and ST-037 match. It cannot see ST-011's lost tax code, because a total of net_sales knows nothing about tax codes. That is the next step's job.

The date range is deliberate. Write WHERE BUSINESS_DATE = DATE '2026-11-02' instead and ST-037 vanishes from the ERP side, because its DATE holds 06:15 as well. You would phone the store about a sale that was posted all along.

#Step 3: one hash per row that both engines agree on

To catch a change in any column without moving every row, compute one hash per row on each side and bring only the Oracle hashes across. SQL Server has HASHBYTES; Oracle has STANDARD_HASH, from Oracle Database 12c, with SHA256 among its methods (the default is SHA1, so name it). Both give the same SHA-256 for the same bytes. The work is making sure the bytes are the same.

So build the text by hand, the same way in both engines: amounts with two decimals, NULL and empty both written as ~, a | between columns, codes trimmed. Then turn the hash into upper-case hex on both sides, so you compare text with text.

DECLARE @day date = '2026-11-02';

DECLARE @oracle nvarchar(4000) = N'
SELECT TRIM(STORE_CODE) AS STORE,
       RAWTOHEX(STANDARD_HASH(
         TO_CHAR(NET_SALES, ''FM9999999990.00'') || ''|'' ||
         TO_CHAR(UNITS)                          || ''|'' ||
         NVL(TAX_CODE, ''~''), ''SHA256'')) AS ROW_HASH
FROM ERP.ERP_SALES
WHERE BUSINESS_DATE >= DATE ''' + CONVERT(nchar(10), @day, 23) + N'''
  AND BUSINESS_DATE <  DATE ''' + CONVERT(nchar(10), DATEADD(day, 1, @day), 23) + N'''';

CREATE TABLE #erp_hash (store varchar(10), row_hash char(64));

DECLARE @sql nvarchar(max) = N'
INSERT INTO #erp_hash (store, row_hash)
SELECT STORE, ROW_HASH
FROM OPENQUERY(ERPORA, ''' + REPLACE(@oracle, N'''', N'''''') + N''')';
EXEC (@sql);

WITH t AS (
  SELECT store,
         CONVERT(char(64), HASHBYTES('SHA2_256', CONCAT(
           CONVERT(varchar(30), net_sales), '|',
           CONVERT(varchar(12), units),     '|',
           COALESCE(NULLIF(tax_code, ''), '~'))), 2) AS row_hash
  FROM dbo.till_sales
  WHERE business_date = @day
)
SELECT COALESCE(t.store, e.store) AS store,
       CASE
         WHEN e.store IS NULL THEN 'Missing in ERP'
         WHEN t.store IS NULL THEN 'Missing at tills'
         ELSE 'Differs'
       END AS result
FROM t
FULL OUTER JOIN #erp_hash AS e ON e.store = t.store
WHERE t.store IS NULL
   OR e.store IS NULL
   OR t.row_hash <> e.row_hash
ORDER BY 1;

It returns ST-011, ST-014, ST-022, ST-031 and ST-047. ST-004 and ST-037 hash the same on both engines.

Prove the two sides agree before you trust it. Run one known string through each engine:

-- SQL Server
SELECT CONVERT(char(64), HASHBYTES('SHA2_256', '9850.20|412|STD'), 2);

-- Oracle
SELECT RAWTOHEX(STANDARD_HASH('9850.20|412|STD', 'SHA256')) FROM dual;

Both should return 2F1E8215DD60D1A004933B7FC217985B6CFB21A4C56CF9F69B6E1799A06E2FB6. If they don't, every row will "differ", and the fault is in the text, not the data.

Keep hashed text to plain ASCII. A hash covers bytes, and an accented name is stored as different bytes in a varchar column, an nvarchar column and an Oracle database in UTF-8. Hash codes, numbers and dates, and compare free text as values with the join from the SQL tables post.

A hash has no tolerance. ST-031's 0.04 shows as a changed row. Step 2 already told you it was rounding.

#The five traps between SQL Server and Oracle

Each of these makes equal rows look different, or hides a real difference.

Oracle also folds unquoted names to upper case, so the columns come back across the link as STORE and ROW_HASH whatever case you typed. And the FM in the number format removes the leading blank that TO_CHAR keeps for a sign; without it, every amount gains a space and every hash differs.

#Without a linked server

Many DBAs won't add one, and on Azure SQL Managed Instance Microsoft says linked servers reach only SQL Server, SQL Database and other managed instances. Then export the same day from each side, with the same formats: the date as YYYY-MM-DD, amounts with two decimals, codes trimmed, empty text written the same way. Do the formatting in each export query, using the same expressions as in step 3, so the files are already alike when they land. Then compare them as files, which the post on comparing two CSV files walks through.

#The worked example

Run steps 2 and 3 against the two tables and put the answers side by side:

Invented figures. Six rows on each side and a gap of 4,498.49 for the day, which on its own tells you nothing about where to look. Totals find four stores, the hash adds ST-011, and together they tell rounding apart from a real change. Written with = DATE '2026-11-02', both steps would also have called ST-037 missing in the ERP.

#Where doing it by hand runs out

You now have a linked server, two dynamic queries with quotes inside quotes, and a list of five stores. Tomorrow at six someone has to run them again, remember that ST-031 is always a few cents out, and find out why ST-014 is 480 short, which means opening the ERP's error log and phoning the store.

DataMosaic reads SQL Server and Oracle directly, with no linked server. A check lines the two sides up on shared keys and shows matched, differing and missing rows, and its rules say what counts as a failure. Normalise transforms such as Trim spaces and Date only, drop the time make Oracle's padded codes and timed dates read like the tills' before anything is compared. On a differing row, an investigation walks through the reasons the two systems can disagree, such as a rejected record or data that hasn't arrived yet. A check only reads; Mosaic changes a system only through a workflow you build.

#Questions people ask

#How do I compare data between SQL Server and Oracle?

Add Oracle as a linked server with the OraOLEDB.Oracle provider, pull counts and totals per key through OPENQUERY, and compare them with a full outer join. For column-level changes, compare a SHA-256 hash per row built from the same text on both sides.

#Can I join a SQL Server table to an Oracle table directly?

Yes, with a four-part name through a linked server, but the filtering may happen in SQL Server after the rows have crossed. Send the query to Oracle with OPENQUERY, land the result in a temporary table, and join locally.

#Why do my hashes never match between SQL Server and Oracle?

Because the text hashed differs, usually a number written as 12400 on one side and 12400.00 on the other, a padded CHAR, or a NULL handled differently. Hash one known string on both engines first; if those agree, the fault is in how the row is built.

#Does Oracle treat an empty string as NULL?

Yes. Oracle's documentation says a character value of length zero is treated as NULL, and warns this may change, so write NULLIF(col, '') on the SQL Server side to make both sides agree.

#Does Oracle have EXCEPT?

Current Oracle documentation lists EXCEPT as a synonym for MINUS, with EXCEPT ALL and MINUS ALL too. Either only works when both tables are in the same database.