Dynamics GP SQL Database Structure: A Map for Migration Planning

Dynamics GP stores data across hundreds of SQL Server tables following naming conventions designed in the late 1980s. If you are planning a migration to Business Central, NetSuite, Acumatica, or Sage Intacct, the first thing you need is a reliable map of where the data actually lives. This post is that map.

GP's SQL database has no foreign key constraints at the database level — all referential integrity is enforced by the Dexterity application layer. Table names are cryptic alphanumeric codes like GL20000, RM00101, and PM00200. There are no ER diagrams shipped with the product that a standard DBA can open in SSMS. Data is split across a system database and one or more company databases, making multi-entity extraction non-trivial.

With Microsoft ending new perpetual license sales by April 2025 and mainstream support ending December 31, 2029, IT directors and migration consultants are under pressure to map and extract decades of historical data. For the full timeline, see Dynamics GP End of Life: 2025–2031 Timeline & Migration Plan.

If you treat a GP migration as a simple CSV export-and-import, your general ledger will not balance, your segment-based reports will break, and your finance team will spend weeks reconciling. You need to understand how GP stores master data, subledger details, and historical records to extract them with relational integrity intact.

Decoding Dynamics GP Table Naming Conventions

Every GP table name has two parts: a letter prefix identifying the module and a five-digit number identifying the table type and lifecycle stage. (learn.microsoft.com)

Module prefixes you will encounter during migration:

Numeric ranges define the table type:

So GL20000 is General Ledger, Open Year Posted Transactions. PM00200 is Payables Management, Vendor Master. SOP30300 is Sales Order Processing, Historical Line Detail. Once you internalize this pattern, you can navigate GP's SQL database without memorizing every table name.

One pattern that catches people: GP often splits a logical object into header and detail tables. GL10000 is the work header, GL10001 is the work detail. SOP10100 is the SOP header, SOP10200 is the line detail. If you only extract headers, you get documents without accounting or item lines. If you only extract detail, you lose customer, vendor, batch, and posting context. (victoriayudin.com)

General Ledger Tables (GL): The Financial Core

The GL tables are the most critical extraction target for any migration. Every subledger (AP, AR, Sales, Purchasing) posts into the GL, so this is where your trial balance lives.

Chart of Accounts:

• GL00100 — Account Master. Contains the account index (ACTINDX), account description (ACTDESCR), account type (ACCTTYPE: 1 = Posting, 2 = Unit), account category (ACCATNUM), and individual segment fields (ACTNUMBR_1 through ACTNUMBR_5).
• GL00105 — Account Index Master. Contains the concatenated, human-readable account number string (ACTNUMST). This is the table you join to when you need the full account number from any transaction table.
• GL00102 — Account Category Master. Links account categories to descriptions.

Transactions:

• GL10000 / GL10001 — Work (Unposted). GL10000 is the header; GL10001 is the line detail. These hold transactions sitting in unposted batches. Header carries fields like JRNENTRY, TRXDATE, BACHNUMB, SOURCDOC, CURNCYID, and USWHPSTD. Detail carries ACTINDX, DEBITAMT, CRDTAMNT, DSCRIPTN.
• GL20000 — Open Year Posted Transactions. All posted journal entries for years that have not been closed. Key fields: JRNENTRY (journal entry number), ACTINDX (account index), DEBITAMT, CRDTAMNT, TRXDATE, SOURCDOC, OPENYEAR, REFRENCE, USWHPSTD (user who posted).
• GL30000 — Historical Year Transactions. Same structure as GL20000 but for years that have been closed through year-end processing. Uses HSTYEAR instead of OPENYEAR.

Summary tables:

• GL10110 — Open Year Summary Master (period balances for open years)
• GL10111 — Historical Year Summary Master

Here is how to extract all posted GL transactions with resolved account numbers:

SELECT
  gl.JRNENTRY,
  gl.TRXDATE,
  a.ACTNUMST AS AccountNumber,
  m.ACTDESCR AS AccountDescription,
  gl.DEBITAMT,
  gl.CRDTAMNT,
  gl.SOURCDOC,
  gl.REFRENCE,
  gl.ORTRXTYP,
  gl.USWHPSTD
FROM GL20000 gl
INNER JOIN GL00105 a ON gl.ACTINDX = a.ACTINDX
INNER JOIN GL00100 m ON gl.ACTINDX = m.ACTINDX
WHERE m.ACCTTYPE = 1  -- Posting accounts only

Replace GL20000 with GL30000 and OPENYEAR with HSTYEAR to pull historical years. For a complete trial balance or historical ledger, your extraction must UNION ALL the GL20000 and GL30000 tables. Include GL10000/GL10001 only if you are intentionally carrying unposted work into the target.

If a target ERP needs a segmented chart rather than a concatenated account string, extract both the individual segment columns from GL00100 and the concatenated value from GL00105. (victoriayudin.com)

Accounts Receivable Tables (RM): Customer Data

• RM00101 — Customer Master. The primary customer record: CUSTNMBR (customer ID), CUSTNAME, CUSTCLAS (class), STMTNAME, CRLMTAMT (credit limit), BALNCTYP, PRCLEVEL (price level).
• RM00102 — Customer Address Master. Multiple addresses per customer, keyed on CUSTNMBR + ADRSCODE. The customer master uses PRSTADCD to point to the primary shipping address. Contains addressable data you need to rebuild customers: contact name, street lines, city, state, ZIP, country, phone numbers, tax schedule, site, salesperson, and territory.
• RM00103 — Customer Master Summary.
• RM20101 — Open Transactions. Outstanding (unpaid) invoices, debit memos, and credits. Key field: CURTRXAM (current transaction amount — the outstanding balance).
• RM30101 — Historical Transactions. Paid and closed receivables.
• RM20201 — Open Apply records. Tracks how payments are applied to open documents.
• RM30201 — Historical Apply records.

Join RM20101 back to RM00101 on CUSTNMBR to get the customer name alongside open balances. For migration, the open receivables in RM20101 are what you need to bring forward as outstanding AR.

Be careful with summary tables. RM00103 and RM00104 are useful for validation but should not be your sole source of truth for reconstructed subledger history — sales totals can vary with setup options. Use transaction tables instead. (victoriayudin.com)

Accounts Payable Tables (PM): Vendor Data

This is an area where bad table lists cause real trouble.

• PM00200 — Vendor Master. The primary vendor record: VENDORID, VENDNAME, VNDCLSID (vendor class), PYMTRMID (payment terms), TAXSCHID (tax schedule).
• PM00201 — Vendor Master Summary. Not vendor addresses. (victoriayudin.com)
• PM00300 — Vendor Address Master. Multiple addresses per vendor (Primary, Remit-To, Purchase). Join on VENDORID. (victoriayudin.com)
• PM00204 — 1099 Period Detail.
• PM10000 — Work transactions (unposted payables).
• PM20000 — Open Payables. Outstanding AP documents. Key field: CURTRXAM.
• PM30200 — Historical Transactions. Paid and voided payables.
• PM30300 — Apply History. Tracks payment application to invoices.

For vendor extraction, start with VENDORID, VENDNAME, address records from PM00300, and transaction identifiers like DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, PSTGDATE, DOCAMNT, TRXSORCE, and CURNCYID.

Inventory Tables (IV): Items and Quantities

• IV00101 — Item Master. Contains ITEMNMBR (item number), ITEMDESC, ITEMTYPE, ITMCLSCD (item class), UOMSCHDL (unit of measure schedule). Also carries account index fields like IVIVINDX (inventory on hand), IVCOGSIX (cost of goods sold), and IVSLSIDX (sales).
• IV00102 — Item Quantity Master. On-hand quantities by location: QTYONHND, QTYINSVC, QTYRTRND, APTS (location code). Note: RCRDTYPE = 1 is the overall quantity record and RCRDTYPE = 2 is the site-level record — this matters if you are rebuilding per-location balances. (victoriayudin.com)
• IV00103 — Item Vendor Master. Links items to vendor sourcing.
• IV10200 — Inventory Transaction Amounts Work.
• IV30300 — Inventory Transaction Amounts History.

When extracting inventory for migration, join IV00101 to IV00102 on ITEMNMBR to get item details with quantity-by-site.

Sales Order Processing Tables (SOP)

There is a strict distinction between SOP and RM in Dynamics GP. SOP handles inventory allocation, quoting, and invoice generation. RM handles the financial side: AR aging, cash receipts, and customer balances. When a SOP document is posted, the SOP record moves to History (SOP30200/SOP30300), and the financial impact creates a new open receivable in RM20101.

• SOP10100 — Work transactions (header). Unposted sales orders, quotes, invoices. Key fields: SOPNUMBE (document number), SOPTYPE (1=Quote, 2=Order, 3=Invoice, 4=Return, 5=Back Order, 6=Fulfillment Order), CUSTNMBR, DOCDATE, GLPOSTDT, CSTPONBR, BACHNUMB, LOCNCODE.
• SOP10200 — Work transactions (line detail). One row per line item. Joined to header on SOPTYPE + SOPNUMBE, with LNITMSEQ controlling line order. Key line fields: ITEMNMBR, ITEMDESC, QUANTITY, QTYREMAI, UNITPRCE, UNITCOST.
• SOP30200 — Historical transactions (header). Posted invoices and returns.
• SOP30300 — Historical transactions (line detail).
• SOP60100 — SOP-POP Link. Tracks which PO lines are linked to which sales order lines. If your business uses SOP-POP links for fulfillment or procurement flow, include this table — otherwise, item demand and supply history will look disconnected across modules.

Purchase Order Processing Tables (POP)

• POP10100 — PO Work (header). Open and in-process purchase orders: PONUMBER, VENDORID, POTYPE (1=Standard, 2=Drop-Ship, 3=Blanket, 4=Drop-Ship Blanket), POSTATUS.
• POP10110 — PO Line items. Not POP10200 — this is a common mistake in GP table references. (victoriayudin.com)
• POP30100 — Historical receipts (header).
• POP30110 — Historical receipts (line detail).
• POP30300 — Historical POs (header).
• POP30310 — Historical PO line items.

If you need actual account numbers from PO lines, follow the line table's account index fields (like INVINDX) back to GL00105.ACTINDX.

Payroll Tables (UPR): Employee and Pay History

• UPR00100 — Employee Master. Core employee record: EMPLOYID, FRSTNAME, LASTNAME, SOCESSION (SSN — handle with extreme care), DEPRTMNT, JOBTITLE.
• UPR00102 — Employee Address Master.
• UPR30100 — Check History. One record per payroll check issued.
• UPR30300 — Transaction History. Detailed payroll transaction records per pay code per check.
• UPR30301 — Transaction History Header.
• UPR30200 — Tax Liability History. Used in quarter-end and 941 reporting.
• UPR30400 / UPR30401 — Distribution History (GL posting distributions for payroll).

Do not treat UPR30100 alone as complete payroll history. You usually need both check-level and transaction-level tables to preserve auditable payroll detail. (victoriayudin.com)

Payroll data is the most sensitive data in GP. SSNs, salary history, tax withholdings, and garnishment records all live in UPR tables. Any migration that touches payroll must account for encryption, access controls, and compliance with regulations like GDPR and CCPA. See Payroll Data Migration Security & Compliance for the full framework.

System Database (DYNAMICS) vs. Company Databases

This is where many migration consultants get tripped up. GP does not store everything in one database.

The DYNAMICS system database (sometimes given a custom name) holds:

• SY01500 — Company Master (list of all companies and their database names)
• SY01400 — Users Master
• SY60100 — User-Company Access
• SY09000 / SY09100 / SY09200 — Security tasks, roles, and alternate permissions
• MC40000 — Multicurrency setup
• Intercompany relationship configurations
• Exchange rate tables

Each company database holds its own complete set of GL, PM, RM, SOP, POP, IV, UPR, and setup tables. If your GP installation has five companies, you have five separate SQL databases — each containing its own GL20000, RM00101, PM00200, and so on. They do not natively share transactional data. Company databases are typically named with brief alphanumeric codes (e.g., TWO, PROD1, UK01). The SY01500 table maps these cryptic database names (INTERID) to the actual legal entity names (CMPNYNAM). (learn.microsoft.com)

When migrating to a modern cloud ERP like Business Central or NetSuite — which typically use a single database with a dimension or subsidiary structure — you must extract and consolidate data across all GP company databases.

Querying Across Multiple Companies

Start every multi-company extraction by inventorying the estate:

SELECT INTERID, CMPNYNAM
FROM DYNAMICS..SY01500
WHERE INTERID NOT LIKE '%TEST%'
ORDER BY INTERID

INTERID is the company database name. Then for each company database, run your extraction queries against that specific database:

SELECT 'CompanyA' AS SourceCompany, a.ACTNUMST, m.ACTDESCR, m.ACCTTYPE
FROM CompanyA..GL00105 a
INNER JOIN CompanyA..GL00100 m ON a.ACTINDX = m.ACTINDX
 
UNION ALL
 
SELECT 'CompanyB' AS SourceCompany, a.ACTNUMST, m.ACTDESCR, m.ACCTTYPE
FROM CompanyB..GL00105 a
INNER JOIN CompanyB..GL00100 m ON a.ACTINDX = m.ACTINDX

For organizations with 10+ companies, generate these cross-database queries dynamically using SY01500 as the driver, appending a company code column to maintain data lineage.

Keep company separation visible in your staging layer until entity mapping is final. Merging companies too early is how teams lose source-system provenance, intercompany trails, and per-company accounting differences. For the detailed BC migration path, see Dynamics GP to Business Central: Migrating 20 Years of History. For global organizations managing multiple entities with different currencies, see Multi-Entity, Multi-Currency ERP Migration: Where Global Companies Fail.

The DEX_ROW_ID Trap

Every Dexterity-created table in GP has a column named DEX_ROW_ID — an INT IDENTITY column with a maximum value of 2,147,483,647. It was added when GP transitioned from ISAM (Btrieve/Ctree) to SQL Server to provide a unique row identifier and support Dexterity's Optimistic Concurrency Control (OCC). (willoware.com)

Many migration scripts and integration tools use DEX_ROW_ID as a primary key or a high-water mark for delta extractions. Do not do this. Here is what you need to know:

1. It is unique within a single table at any point in time. SQL Server enforces this as an identity column. It is not unique across the GP database estate. (community.dynamics.com)
2. It is NOT persistent across upgrades or maintenance. When GP upgrades a table, or when an administrator runs the "Check Links" utility, GP copies data to a temp table, drops and recreates the original table, then inserts the data back. The INSERT does not include DEX_ROW_ID, so all rows get new values renumbered from 1.
3. High-volume tables can overflow. The identity column increments on insert and does not shrink when rows are deleted. GP installations with large payroll or inventory transaction volumes can approach the INT max, causing an arithmetic overflow error that blocks all inserts to the table.
4. Delta syncs built on DEX_ROW_ID break silently. If you build an integration that tracks "last migrated DEX_ROW_ID" to detect new records, your sync will break the next time that table is rebuilt during a service pack or version upgrade — every record will appear new, causing massive duplication in your target ERP.

No Foreign Keys: What This Means for Extraction

GP's SQL database has zero foreign key constraints at the database level. All referential integrity is enforced by the Dexterity runtime. This means:

• You cannot use SSMS database diagrams to discover table relationships.
• Orphaned records are possible and more common than you would expect, especially in installations with years of customization and direct SQL manipulation.
• Before extracting, run validation queries to confirm referential integrity.

For example, verify every ACTINDX in GL20000 exists in GL00100:

SELECT DISTINCT gl.ACTINDX
FROM GL20000 gl
LEFT JOIN GL00100 m ON gl.ACTINDX = m.ACTINDX
WHERE m.ACTINDX IS NULL

If this returns rows, you have orphaned GL transactions pointing to deleted accounts. These must be resolved before migration — every target system that enforces referential integrity (which is all of them) will reject these records on import.

GP Data Extraction Methods Compared

Microsoft Cloud Migration Tool (Azure Data Factory): The native GP-to-BC path. It relies on Azure Data Factory with a self-hosted Integration Runtime for on-prem SQL Server and requires SQL Server 2016 or later at compatibility level 130 or higher. Microsoft documents migration support for customers, vendors, items, historical data, outstanding receivables and payables, 1099 data from 2024 onward, and open purchase orders based on remaining quantities. (learn.microsoft.com)

The tool handles standard GP tables and maps them into Business Central's data model, but it will not pick up ISV tables, custom fields added directly to SQL, or Analytical Accounting data structures (the AAG series) without custom table mapping. If you rely on 3rd-party modules like Mekorma, Binary Stream, or eOne, those custom SQL tables get skipped entirely. For more on auditing these limitations, see How to Audit Dynamics GP Customizations Before Migration.

KingswaySoft SSIS Toolkit: Provides SSIS connectors that abstract GP's table structure into named entities. Understands GP's eConnect endpoints and handles data validation cleanly. Because it often routes through eConnect for certain operations, extracting 20 years of historical ledger data can bottleneck and take days. Useful if your team already works in the SSIS ecosystem, but for migrations to non-Microsoft targets, you are building the transformation logic yourself. (kingswaysoft.com)

eOne SmartConnect: The standard integration tool used by most GP partners. Code-free mapping interface, reliable for daily transactional syncing between GP and external CRMs like Salesforce. Not designed for bulk historical data migration — using SmartConnect to move millions of GL30000 rows will result in severe performance degradation and timeouts.

Direct SQL extraction gives you full control and works for any destination. This is what ClonePartner uses.

Quick Reference: Migration-Critical Tables by Module

How ClonePartner Handles GP Database Extraction

Our migration team works directly with GP's SQL database structure. Here is how we approach it:

1. Discovery: We connect to your SQL Server (read-only) and profile every company database — table counts, row counts, date ranges, and ISV footprint.
2. Pre-built extraction queries: We do not start from scratch. We have tested extraction queries for GL, AP, AR, Inventory, Sales, Purchasing, and Payroll that resolve ACTINDX values, handle the Work/Open/History split, and validate referential integrity before a single record is exported.
3. Cross-company consolidation: For multi-entity GP installations, we dynamically generate extraction scripts across all company databases, tagging each record with its source company for clean mapping into a unified target.
4. Segment-to-dimension mapping: We map GP's segmented account strings into modern dimensional structures, ensuring that your trial balance matches to the penny on day one.
5. ISV inclusion: If your GP environment is heavily customized with ISV tables, we audit the schema and include those custom SQL tables in the extraction payload.
6. Zero downtime: We extract from a read-only SQL replica or snapshot. Your GP users never know we are there. No batch holds, no locked tables, no disrupted posting.

What to Do Next

A solid GP migration plan starts with four decisions: which company databases exist, which tables are the true system of record for each object, which history stays detailed versus summarized, and which join keys survive in the target. If you cannot answer those four questions for GL, customers, vendors, inventory, open orders, and payroll, you are not ready to script extraction yet.

If you are early in planning, start by running SELECT COUNT(*) FROM [table] against the key tables listed above in each company database. This gives you a volume baseline — critical for estimating extraction time, transformation complexity, and load duration in your target system.

For teams moving to Business Central, the Microsoft Cloud Migration Tool handles a portion of this mapping, but you need to understand what it covers and what it skips. For the full path, see Dynamics GP to Business Central: Migrating 20 Years of History.

For teams moving to NetSuite, Acumatica, or Sage Intacct, there is no automated tool. You are doing direct SQL extraction and building the transformation layer. That is the work ClonePartner specializes in.