Data Compare

functional

Compare table data across connections and generate a review-only sync script.

Compare the data in tables across connections. Each side picks a connection, a database, and either a table or, via the Use a query checkbox - an arbitrary SELECT. Key columns come from the left table's primary key automatically, or you name them yourself; both sides then stream in key order through a merge-join, so the comparison scales past what fits in memory.

Differing rows land in the grid classified as added, removed, or changed - with per-column before and after values - under filter chips for All, Only left, Only right, and Changed, and a summary line counting each class. Script Sync to Editor then generates the matching INSERT / UPDATE / DELETE statements for the direction you chose (left → right or right → left) as a review-only script, so you can reconcile two databases deliberately.

SQLly
SQLly Data CompareSQLly Data Compare
Data Compare: matched tables across two connections, showing added, removed, and changed rows with a review-only sync script.

Comparing staging with production

  1. Right-click a database and choose Data › Data Compare... (or run Data Compare... from the command palette). The current connection is preselected on the Left; the Right starts on another saved connection.
  2. On each side pick the connection and database, then type the table as schema.table, for example dbo.PriceList. Type it the way the catalog stores it: on PostgreSQL and Oracle, orders and ORDERS are different tables.
  3. Leave the key box blank to use the left table's primary key, or enter key columns separated by commas.
  4. Press Compare. The summary line reads like 3 only left · 1 only right · 12 changed · 4980 unchanged.
  5. Use the chips to narrow the grid, choose a direction, Sync left → right or Sync right → left, and press Script Sync to Editor.

Behind the scenes each side runs a plain ordered read, quoted for its own engine:

SELECT * FROM [dbo].[PriceList] ORDER BY [PriceListId]

With Use a query checked, your SELECT is wrapped the same way - SELECT * FROM (your query) AS sqlly_cmp ORDER BY ... - so you can compare a filtered slice or a join. Query mode needs explicit key columns.

Reading the results

ColumnMeaning
ClassificationRemoved (only on the left), Added (only on the right), or Changed.
KeyThe row's key values, separated by |.
ColumnFor a changed row, the column that differs - one grid row per changed cell. (row) for whole added or removed rows.
Before / AfterThe left and right values. Added rows have only an After; removed rows only a Before.

Columns are matched by name, ignoring case; every left column must exist on the right.

The sync script

The script opens in a new query tab connected to the destination side's connection and database. Nothing runs until you press Run. For left → right it inserts rows only on the left, deletes rows only on the right, and updates just the changed columns of changed rows:

-- Data compare sync: Staging / [shop] (left) vs Production / [shop] (right)
-- Direction: left -> right. Review carefully; nothing runs until you press Run.
-- 1 insert(s), 1 update(s), 1 delete(s), 0 skipped
BEGIN TRANSACTION;
INSERT INTO [dbo].[PriceList] ([PriceListId], [Sku], [Qty]) VALUES (41, N'A-100', 5);
DELETE FROM [dbo].[PriceList] WHERE [PriceListId] = 77;
UPDATE [dbo].[PriceList] SET [Qty] = 12 WHERE [PriceListId] = 12;
COMMIT;
  • The transaction wrapper follows the engine: START TRANSACTION on MySQL and MariaDB, BEGIN on PostgreSQL, SQLite and DuckDB, and only a closing COMMIT on Oracle.
  • ClickHouse has no transactions, so each statement stands alone, and updates are written as ALTER TABLE … UPDATE mutations.
  • Rows with a binary value in a needed column are skipped and listed as comments with their key, so you can handle them by hand.
  • The destination must be a table, not a query; the button is disabled otherwise.

Limits

  • A compare stops after 1,000,000 rows on either side or 50,000 differences. The summary and the sync script both say it was truncated, and the script warns that it is incomplete, narrow it with a query.
  • Closing the dialog mid-compare cancels both queries on the servers.
  • Choosing the same connection, database, and table on both sides is refused.
  • Data Compare is not available in the browser preview; use the desktop app.

Key problems are reported honestly, missing, NULL, or duplicate key values, or rows arriving out of order (usually a collation mismatch) stop the comparison with a specific message rather than a wrong answer.

Supported on SQL Server and Azure SQL, PostgreSQL, MySQL/MariaDB, SQLite, libSQL / Turso, DuckDB, ClickHouse, and Oracle Database. Redis has no tables to compare; use the key browser and GET / HGETALL.