Last updated: 2026-02-12

Data Compare

Compare table data between databases to identify row-level differences. Find missing, modified, and extra rows with visual diff highlighting. Generate INSERT, UPDATE, and DELETE scripts to synchronize data.

Getting Started

Data Compare lets you compare the actual data in your tables, not just the schema structure. This is useful for verifying data migrations, auditing environments, and synchronizing lookup tables across databases.

How to Start a Data Comparison

1. Open Jam SQL Studio and connect to both source and target databases
2. Click Tools > Data Compare from the main menu
3. Select the source connection and choose the table to compare
4. Select the target connection and choose the corresponding table
5. Configure the key columns used to match rows between tables
6. Click Compare to analyze the data differences

Tip: The table dropdowns are searchable — start typing to filter, use ↓ to move into the list, and press Enter to select.

Launch from a Database Blueprint. Data Compare can also be opened straight from a Database Blueprint: in the Sync Data dialog, the per-table Open in Data Compare action opens a comparison with the blueprint folder's captured data as the source and the linked database as the target, with key/value mappings pre-filled and the comparison auto-run. From there you work with the same results grid and sync-script tools described below.

Personal Mode Limits

Data Compare is available in both Personal and Pro modes, with different limits:

• Personal mode - Full Comparison is available when both tables have 10,000 rows or fewer. For larger tables, use Sample Compare or upgrade to Pro.
• Pro mode - No row-count limits for Full Comparison.

Understanding the Results Grid

After the comparison completes, you'll see a grid showing all rows from both tables with their comparison status:

If the comparison finds no added, modified, or deleted rows, the results area shows a clear “Source and target data has no differences” message instead of an empty grid — so a clean comparison is never mistaken for one that didn’t run.

Filtering and Navigation

• Use the filter buttons to show only added, modified, or deleted rows
• Click column headers to sort the comparison results
• Use the search box to find specific values across all columns
• Navigate between differences using the previous/next buttons

Generating Sync Scripts

Once you've reviewed the differences, generate SQL scripts to synchronize the target table with the source data.

Steps to Generate a Script

1. Review the differences in the results grid
2. Check or uncheck individual rows to include or exclude them
3. Click Generate Script to preview the DML statements
4. Review the generated INSERT, UPDATE, and DELETE statements
5. Click Apply to execute directly, or Save to export

Comparison Options

Key Column Selection

By default, Jam SQL uses the primary key to match rows. You can customize this by selecting different columns as the comparison key. This is useful when:

• Tables have different primary key definitions
• You want to compare based on natural keys (e.g., email, product code)
• The target table lacks a primary key constraint

Column Exclusions

You can exclude columns from the comparison, which is helpful for:

• Auto-generated columns (timestamps, audit fields)
• Columns with environment-specific values
• Binary or large text columns you don't need to compare

Best Practices

• Always backup your target database before applying data changes
• Test with small datasets before comparing large tables
• Use transactions - generated scripts include BEGIN/COMMIT for safety
• Review DELETE statements carefully before execution
• Consider foreign keys - ensure related data is synchronized in the correct order

Frequently asked questions