SYSPRO SQL Table Map for Data Migration: A Technical Guide

SYSPRO 8 stores all its data in Microsoft SQL Server, in one system-wide database and one database per SYSPRO company. SYSPRO's own database guide says "each SYSPRO 8 company database contains approximately 1000 tables" (help.syspro.com). Only a small set of those SYSPRO SQL tables hold the master and transactional data a migration moves. This guide maps those tables by module. Every table name below appears in SYSPRO's own documentation, and each row links to the page where it does. The guide also covers the edge cases that break migrations when they are missed: custom form "plus" tables, exchange rates, lot and serial tracking, and unenforced foreign keys.

The deadline is close. SYSPRO is ending its Initial License Fee and Annual License Fee model and moving to a named-user subscription. Existing licences expire on 31 October 2026 for customers outside the US and 31 December 2026 for customers in the US. After that date "you will no longer be able to access Syspro" unless you sign a new subscription agreement (syspro.com).

For every extraction method, see How to Export Data from SYSPRO: Methods, API Limits & Portability. For a side-by-side view of the two main routes, see SYSPRO e.net Business Objects vs Direct SQL for Data Extraction.

How Is SYSPRO's Database Structured?

SYSPRO uses one system-wide database and one database per SYSPRO company. A site with three companies has four databases. SYSPRO's guide says all of them "must be located on a single instance of Microsoft SQL Server" (help.syspro.com).

The system-wide database "contains over 100 tables depending on the features installed". It holds operators, licence information, security settings and audit data. Data dictionary tables start with Dds, Report Writer tables with Rep, Espresso mobile tables with Esp and Avanti tables with Avt. The SysproAdmin table holds the "Company / Database / Collation cross-reference" (help.syspro.com). Start there to find every company database on the site.

The company database holds master and transactional data for that company. Table names start with a module mnemonic: Ar for Accounts Receivable, Ap for Accounts Payable, Inv for Inventory, Sor for Sales Orders, Por for Purchase Orders, Bom for Bill of Materials, Wip for Work in Progress and Gen for General Ledger. SYSPRO table names have a maximum of 18 characters (help.syspro.com).

Which SYSPRO SQL Tables Do You Need for a Migration?

The tables below are the ones SYSPRO names in its documentation. Your company database has many more. Use the discovery queries later in this guide to find the rest of your scope.

Accounts Receivable (Customers)

ArCustomer is the anchor of the AR module. SYSPRO's guide uses it as its foreign key example: ArInvoice stores the customer in its Customer column, and the key Syspro_FK_ArInvoice_ArCustomer describes that link (help.syspro.com). Extract ArCustomerGroup to rebuild your customer classification in the new system. Pull ArInvoice if your scope includes historical sales invoices or open AR balances.

Accounts Payable (Suppliers)

Open AP comes from ApInvoice. Reconcile it against your AP control account so liabilities land correctly in the target ERP.

Inventory

Inventory is often the most complex domain to migrate. InvMaster defines each stock code. InvWarehouse holds the per-warehouse records for those stock codes. If your target system has a different warehouse or location model, this pair is where the structural translation happens.

Few teams load full InvMovements history into a new ERP. More often it goes to an archive or a data warehouse. If you track serialised stock, map InvSerialHead to the target's serial entities. Lot history needs a check first: SYSPRO's lot Purge and Archive program deletes and archives lot transactions, so some history may already sit outside the live tables (help.syspro.com).

Sales Orders

SorMaster and SorDetail follow a header and line pattern. Use the Syspro_FK_ keys (query below) to confirm how they join to each other, to customers and to stock codes. Then produce a denormalised extract your target system can load.

Purchase Orders

This is the same header and line pattern as sales orders. Confirm the joins to suppliers and stock codes from the Syspro_FK_ keys.

Bill of Materials and Manufacturing

Manufacturing data is where SYSPRO migrations get structurally complex. BOM and WIP tables reference inventory, work centres and routings in ways that rarely map one-to-one to the target system. Epicor Kinetic, SAP Business One and Dynamics 365 Business Central each model BOMs, routings and job costing differently. To migrate open WIP, extract the material and labour allocations and translate them into the target's production order format. The BOMs and routings guide works through this translation. For the pair-specific mapping, see SYSPRO to Epicor Kinetic and SYSPRO to Microsoft Dynamics 365 Business Central.

General Ledger

GenMaster defines the chart of accounts. GenTransaction holds the posted transactions. Most teams load opening balances into the new ERP and keep full GenTransaction history in an archive or data warehouse.

Currencies

If you run SYSPRO in more than one currency, put both tables in scope. To keep the exact rate used on historical foreign currency transactions, you need TblCurrDatedRates. Without these tables, opening balances in other currencies will not reconcile.

What Are SYSPRO's Custom Form "Plus" Tables?

SYSPRO stores custom form data in a separate table per form type, named after the primary table with a + suffix. Custom fields on customers live in ArCustomer+, and custom fields on suppliers live in ApSupplier+ (help.syspro.com). SYSPRO advises surrounding these names with square brackets, for example [ArCustomer+], so SQL does not treat the + as an addition symbol.

SYSPRO's guide says the column names in these tables "are described by the end-user" and change as the custom form designer adds, changes or deletes fields (help.syspro.com). Your migration script must discover their structure at runtime:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '%+'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Then find each plus table's key columns before you join it to its primary table:

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '%+'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
 
-- Pull one plus table whole, then join on the key columns found above
SELECT * FROM [ArCustomer+];

Older deployments: before SYSPRO 7, custom form data lived in a single table, AdmFormData (help.syspro.com). If you extract from an older version, your mapping must pivot data out of that one table.

Plus tables are a common source of data loss in SYSPRO migrations. Teams that scope only the standard tables miss every custom field the business added over the years. Run the discovery query early. The custom form fields guide covers mapping them to the target system.

How Do SYSPRO's Foreign Keys Work?

SYSPRO 8 creates every foreign key "by specifying 'WITH NOCHECK'", so SQL Server does not verify the rows involved. SYSPRO says the keys exist "purely to describe the relationship between tables", and its business logic enforces data integrity. Foreign keys are named Syspro_FK_ followed by the source table, an underscore and the target table, for example Syspro_FK_ArInvoice_ArCustomer (help.syspro.com).

This matters for migration planning in two ways:

1. The foreign keys map your join paths. Query them to build a dependency graph of the tables you extract. It shows which tables reference which, and the order to load them into the target system.
2. WITH NOCHECK means orphaned rows can exist. Do not assume every ArInvoice row has a matching ArCustomer row. Use LEFT JOIN rather than INNER JOIN in your extraction queries, and flag orphans before the load.

To list the SYSPRO foreign keys in a company database:

SELECT 
    fk.name AS fk_name,
    tp.name AS parent_table,
    cp.name AS parent_column,
    tr.name AS referenced_table,
    cr.name AS referenced_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fk.name LIKE 'Syspro_FK_%'
ORDER BY tp.name, tr.name;

How Do You Extract Data from SYSPRO for Migration?

We recommend three routes, in this order. Direct SQL comes first where you can use it. OData comes next where you cannot. e.net business objects suit integration, not bulk extraction.

A one-off, read-only SQL extract

SYSPRO's best-practice guide warns: "Connecting to the SYSPRO databases directly using SQL drivers is not advised" (help.syspro.com). That warning is about live integrations. A migration extract is different: it runs once and writes nothing back. Use a read-only login, ideally against a restored copy of the company database, so the live system carries no load.

On that basis, SQL is the most complete route for an on-premise SYSPRO. It reaches every table, including the plus tables, and you control the query, the joins and the output format. For hundreds of thousands of inventory movements or historical invoices, it is the practical route.

Syspro OData Service

The OData service is read-only. It "lets you connect securely to SYSPRO data without direct SQL access" (help.syspro.com). SYSPRO says it "is the only mechanism available when applications need access to SYSPRO data in the Syspro Cloud ERP environment" (help.syspro.com). SYSPRO limits it to 5,000 rows "under the Microsoft best practice guidelines for performance and fair use", and paging uses $top and $skip (help.syspro.com). Large tables therefore need many paged calls.

Business Activity Queries (BAQs)

"SYSPRO Business Activity Queries are SQL views that are created in the relevant company databases", and "any SQL user with db_datareader permissions can retrieve data using the views" (help.syspro.com). BAQs give you pre-joined datasets. They are views over the same tables, so they reach nothing that SQL cannot. The Business Activity Query Export program moves query definitions between environments. It does not export data.

e.net Business Objects

e.net query objects such as ARSQRY, INVQRY and SORQRY return XML for one record or a filtered set per call. They suit integration, not bulk extraction. Calling a business object for every historical sales order is far slower than one SQL query and adds load to the application server. For a detailed comparison, see SYSPRO e.net Business Objects vs Direct SQL for Data Extraction.

Common Mapping Pitfalls in SYSPRO Migrations

These patterns break SYSPRO migration projects:

• Missing plus tables. Custom form data is invisible if you scope only the standard tables. Run the INFORMATION_SCHEMA discovery query before you finalise the mapping.
• Archived sales orders. Archived orders sit in XML archive files, not in SorMaster and SorDetail. Find out whether archiving has been run and where the files are.
• Shared GL across companies. With Shared GL, SYSPRO stores the General Ledger data of several companies in one company's database, with a company id in each row (help.syspro.com). Your GenMaster and GenTransaction extract may need to come from another company's database.
• Exchange rate gaps. Check the date coverage of TblCurrDatedRates before you load. Gaps give wrong rates in the target system's revaluation.
• WITH NOCHECK orphans. Run referential integrity checks on the extract before you load it into a target that enforces constraints.

For more data-layer failure patterns, see Why ERP Migrations Fail at the Data Layer: 9 Core Patterns.

How Does the 2026 Licence Change Affect Migration Planning?

SYSPRO is ending its Initial and Annual Licence Fee model. Existing licences expire on 31 October 2026 for customers outside the US and 31 December 2026 for customers in the US. After the expiry date, access to SYSPRO stops unless you sign a new subscription agreement (syspro.com).

The same notice says: "On cessation of your current licence, you have the right to export your data." SYSPRO "will provide a straightforward process for this on request", and asks customers to raise it with their account manager ahead of the expiry date (syspro.com). The notice does not say what format that export takes or how long it stays available. Take your own extract of the tables above before your expiry date rather than relying on it.

Keep two clocks apart. The data migration, extracting from SYSPRO and loading the target system, takes days. Most data migrations complete within 1 to 5 business days, depending on data volume, complexity and API rate limits on the target system. Under 10,000 records can finish in under 24 hours. The ERP implementation, selecting, configuring and rolling out the new system, takes months. Your implementation partner or the vendor runs it. ClonePartner does not implement ERPs.

A read-only extract in hand lets you run unlimited sample migrations, map your plus tables and validate the data in the target system before go-live. Plan go-live for a Monday. The bulk load runs in the days before while SYSPRO stays live. SYSPRO freezes before Monday's working day starts, and a final delta of changed records takes about 30 minutes.

If NetSuite is your target, see SYSPRO to Oracle NetSuite.

What Should Your Next Step Be?

Start with discovery. Connect a read-only login to a restored copy of your SYSPRO company database. Run the foreign key query to map dependencies and the INFORMATION_SCHEMA query to find your plus tables. Then build a table-level list of what is in scope. That list is the foundation of an accurate migration plan.

If you want a second pair of eyes on the mapping, or you would rather hand the extraction and load to an engineer-led team, we can help. ClonePartner charges one fixed price per project, starting at $500, with no hourly billing (clonepartner.com).