# Duplicate Master Record Finder SQL Generator for ERP Tables

> Source: https://netray.co/tools/erp-duplicate-master-record-finder-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your master table and match columns to get a ready-to-run SQL query that groups likely duplicates for review.

## Category

developer

## Audience

- ERP admin
- MDM analyst
- Data steward

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Master table name

**Key:** table

**Type:** text

**Default:** vendor_master

**Help:** Use the real table name from your ERP database. This tool does not assume any vendor schema.

#### Primary key column

**Key:** id_col

**Type:** text

**Default:** vendor_id

#### Name column

**Key:** name_col

**Type:** text

**Default:** vendor_name

#### Tax or registration ID column

**Key:** tax_col

**Type:** text

**Default:** tax_id

#### City or postal column

**Key:** city_col

**Type:** text

**Default:** postal_code

#### Status column

**Key:** status_col

**Type:** text

**Default:** status

#### Active status value

**Key:** active_value

**Type:** text

**Default:** A

**Blocks:**

#### 1. Exact duplicates on tax or registration ID

**Body:** SELECT {{tax_col}} AS match_key,
       COUNT(*) AS record_count,
       MIN({{id_col}}) AS oldest_id,
       MAX({{id_col}}) AS newest_id
FROM {{table}}
WHERE {{tax_col}} IS NOT NULL
  AND LTRIM(RTRIM({{tax_col}})) <> ''
  AND {{status_col}} = '{{active_value}}'
GROUP BY {{tax_col}}
HAVING COUNT(*) > 1
ORDER BY record_count DESC;

#### 2. Likely duplicates on normalised name plus postal code

**Body:** SELECT UPPER(REPLACE(REPLACE(REPLACE(REPLACE({{name_col}}, ' ', ''), '.', ''), ',', ''), '-', '')) AS name_key,
       {{city_col}},
       COUNT(*) AS record_count,
       MIN({{id_col}}) AS first_id,
       MAX({{id_col}}) AS last_id
FROM {{table}}
WHERE {{status_col}} = '{{active_value}}'
GROUP BY UPPER(REPLACE(REPLACE(REPLACE(REPLACE({{name_col}}, ' ', ''), '.', ''), ',', ''), '-', '')), {{city_col}}
HAVING COUNT(*) > 1
ORDER BY record_count DESC;

#### 3. Full detail of every record in a duplicate group

**Body:** SELECT t.{{id_col}}, t.{{name_col}}, t.{{tax_col}}, t.{{city_col}}, t.{{status_col}}
FROM {{table}} t
JOIN (
    SELECT {{tax_col}}
    FROM {{table}}
    WHERE {{tax_col}} IS NOT NULL AND LTRIM(RTRIM({{tax_col}})) <> ''
    GROUP BY {{tax_col}}
    HAVING COUNT(*) > 1
) d ON d.{{tax_col}} = t.{{tax_col}}
ORDER BY t.{{tax_col}}, t.{{id_col}};

#### 4. Count of duplicate groups for the data quality scorecard

**Body:** SELECT COUNT(*) AS duplicate_groups,
       SUM(record_count) AS records_in_groups,
       SUM(record_count - 1) AS surplus_records
FROM (
    SELECT COUNT(*) AS record_count
    FROM {{table}}
    WHERE {{tax_col}} IS NOT NULL AND LTRIM(RTRIM({{tax_col}})) <> ''
    GROUP BY {{tax_col}}
    HAVING COUNT(*) > 1
) g;

**Notes:** Table and column names are placeholders. Check them against your ERP data dictionary before running. The syntax uses LTRIM/RTRIM and standard GROUP BY, which works on SQL Server and most engines; on Oracle replace the empty-string test with IS NOT NULL only, since Oracle treats '' as NULL. Run read-only, on a reporting copy where possible. A shared tax ID can be legitimate (branches, remit-to entities), so treat results as candidates for steward review, never for automatic merge.

## Content

**Intro:** Duplicate vendors, customers and items cause split spend, double payments and wrong inventory. This generator writes the SQL you need to find them in any ERP database: exact matches on a tax or registration ID, fuzzy-ish matches on a normalised name plus postal code, a detail drill-down and a count for your scorecard.

**Sections:**

#### How to use the generator

**Body:** Enter the master table name and the columns that identify a record, its name, its tax or registration number, a postal or city column and a status column. The four query blocks update as you type. Copy them into your SQL client or export the file. Start with block 1 because exact tax ID matches are the highest-confidence duplicates. Then run block 2 to catch the same company entered with different punctuation or spacing. Block 3 lists every record in a group so a steward can decide the survivor. Block 4 gives you the headline numbers for a data quality report.

**Bullets:**

- Use the real table and column names from your ERP data dictionary
- Run on a reporting replica or during a quiet period
- Review group members before any merge or block
- Rerun monthly and track the count of duplicate groups

#### The matching method behind the queries

**Body:** Deduplication works in tiers. Tier one is a deterministic key: a government tax ID, VAT number, GSTIN, DUNS number or bank account. A GROUP BY with HAVING COUNT(*) greater than one finds these exactly. Tier two is a normalised key: uppercase the name, strip spaces, dots, commas and hyphens, then combine with a second attribute such as postal code so that two unrelated companies with the same trading name do not collide. Tier three, which these queries do not cover, is true fuzzy matching with edit distance or phonetic codes, usually done in a dedicated MDM tool or a script.

#### Reading the results

**Body:** A record_count of 2 in block 1 usually means one company was onboarded twice, often by different buyers or in different company codes. The surplus_records figure from block 4 is the number of records that would disappear if every group were merged to one survivor. Pick the survivor by transaction history, not by age: keep the record with open orders, open invoices and the cleanest payment terms. Block (deactivate) the others instead of deleting them, since historic documents still reference them.

#### Common mistakes

**Body:** The most frequent error is merging legitimate branch or remit-to records that share a tax ID. A second is running the name match without a second attribute, which produces huge false-positive groups for generic names. A third is matching on blank values, so always exclude NULL and empty strings. Finally, do not delete duplicates directly in the database: use the ERP's own merge, block or deactivate function so that audit trails, open documents and tax reporting stay consistent.

**Cta:** If you want duplicate detection to run continuously against your ERP with a steward review queue, our team can build it read-only on your own infrastructure.

**Faq:**

#### Does this query change any data?

**Answer:** No. Every block is a SELECT statement. Nothing is updated, merged or deleted. You should still run it with a read-only database account, ideally on a reporting copy, and use your ERP's own merge or block function to act on the findings.

#### Which ERP tables does it work with?

**Answer:** Any relational table, because you supply the table and column names. It suits vendor, customer and item master tables in SAP, Oracle, SyteLine, Epicor, Dynamics, NetSuite exports and custom databases. Always confirm names against your data dictionary first.

#### Why not match on name alone?

**Answer:** Generic names such as 'Global Industries' belong to many unrelated companies. Combining the normalised name with a postal code or tax ID cuts false positives sharply. Name-only matching is fine for a quick scan but never for automatic action.

#### How do I catch typos like 'Acme' and 'Acne'?

**Answer:** These need fuzzy matching with edit distance or phonetic algorithms, which plain GROUP BY cannot do. Use the exact tiers here first, then a script or MDM tool for typo-level matching on the remaining records.

## Search terms

find duplicate vendors sql, duplicate customer records query, erp duplicate detection sql, find duplicate items in erp, master data deduplication query, duplicate vendor tax id query, sql group by having count duplicates

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json