INSERT / UPDATE / DELETE

Syntra ODBC supports write operations that go directly to QuickBooks Desktop via the COM SDK. Unlike read queries that hit the local cache, all writes are executed against QuickBooks in real time. Write access requires the Pro plan.

Primary keys

Every writable entity has either a list_id (for list-backed records like customers, vendors, items, accounts) or a txn_id (for transactions like invoices, bills, journal entries). UPDATE and DELETE operations identify records by these primary keys.

INSERT: Creating Records

Customers

INSERT INTO customers (name, phone, email, bill_address_addr1, bill_address_city, bill_address_state, bill_address_postal_code)
VALUES ('Acme Corporation', '555-0100', 'billing@acme.com', '123 Main St', 'Springfield', 'IL', '62701');

Vendors

INSERT INTO vendors (name, company_name, phone, email)
VALUES ('Office Supplies Co', 'Office Supplies Co LLC', '555-0200', 'ap@officesupplies.com');

Accounts

INSERT INTO accounts (name, account_type)
VALUES ('New Expense Account', 'Expense');

Classes

INSERT INTO classes (name)
VALUES ('Consulting');

Transactions with Line Items

Line-bearing transactions (invoices, bills, sales receipts, credit memos, estimates, sales orders, purchase orders, journal entries, inventory adjustments) must be created with their header and line items in a single qbXML request. Syntra exposes this through the line_ column prefix in SQL, or through the _lines nested array via the MCP server.

Invoice with one line

INSERT INTO invoices
    (customer_ref_full_name, txn_date, ref_number, memo,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-15', 'INV-1001', 'Consulting services',
     'Consulting:Hourly', '10', '150.00');

The line_* columns declare the fields for one line item. Header columns declare the parent fields. Syntra assembles them into a single parent + line insert against QuickBooks.

Bill

INSERT INTO bills
    (vendor_ref_full_name, txn_date, ref_number, memo,
     line_account_ref_full_name, line_amount, line_memo)
VALUES
    ('Office Supplies Co', '2026-06-01', 'BILL-501', 'Monthly supplies',
     'Office Supplies', '99.99', 'Printer paper');

Bills accept expense-line columns (line_account_ref_full_name, line_amount) or item-line columns (line_item_ref_full_name, line_quantity, line_rate), depending on whether you are recording an expense or an item purchase.

Sales Receipt

INSERT INTO sales_receipts
    (customer_ref_full_name, txn_date, deposit_to_account_ref_full_name,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-15', 'Undeposited Funds',
     'Widget-500', '3', '50.00');

Credit Memo

INSERT INTO credit_memos
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-20',
     'Widget-500', '1', '50.00');

Estimate

INSERT INTO estimates
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-06-01',
     'Widget-500', '5', '25.00');

Sales Order

INSERT INTO sales_orders
    (customer_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Acme Corporation', '2026-07-01',
     'Widget-500', '20', '45.00');

Purchase Order

INSERT INTO purchase_orders
    (vendor_ref_full_name, txn_date,
     line_item_ref_full_name, line_quantity, line_rate)
VALUES
    ('Office Supplies Co', '2026-06-01',
     'Widget-500', '100', '10.00');

Journal Entry

Journal entries require balanced debit and credit lines. The MCP server’s preview_journal_entry tool validates the entry before creation.

INSERT INTO journal_entries
    (txn_date, ref_number,
     line_journal_line_type, line_account_ref_full_name, line_amount, line_memo)
VALUES
    ('2026-06-30', 'JE-001',
     'Debit', 'Office Supplies', '100.00', 'April supplies'),
    ('2026-06-30', 'JE-001',
     'Credit', 'Checking', '100.00', 'April supplies');

Note: journal_entries.memo is read-only at the header level. Put descriptive text in the line-level line_memo column.

Available line_* Columns

The set of valid line_* columns depends on the parent transaction type. Common columns across most transactions:

Column	Description
line_item_ref_full_name	Item name (for item lines)
line_item_ref_list_id	Item by ListID
line_quantity	Quantity
line_rate	Unit price
line_amount	Total amount (overrides qty x rate)
line_desc	Line description
line_class_ref_full_name	Class assignment
line_sales_tax_code_ref_full_name	Tax code
line_account_ref_full_name	Account (for expense lines on bills, credit card charges)
line_memo	Line memo
line_journal_line_type	Debit or Credit (journal entries only)

Use SHOW COLUMNS FROM <line_table> to see the full set for a specific parent. For example, SHOW COLUMNS FROM invoice_lines lists every column you can pass as line_<column> on an invoice INSERT.

How line tables work

Line entities (invoice_lines, expense_lines, item_lines, journal_entry_lines, credit_memo_lines, etc.) are child tables attached to their parent transaction. You cannot INSERT or DELETE directly into a line table. Lines are created alongside the parent in a single SQL INSERT using the line_* column pattern shown above, or through the MCP server’s _lines nested array.

A few line tables (invoice_lines, credit_memo_lines, estimate_lines, purchase_order_lines, sales_order_lines, sales_receipt_lines, and their _line_groups variants) do support UPDATE of individual line fields after the parent has been created. Most other line tables (expense_lines, item_lines, journal_entry_lines, inventory_adjustment_lines, deposit_lines, discount_lines, shipping_lines, sales_tax_lines, payroll line tables) are read-only once the parent is posted.

UPDATE: Modifying Records

UPDATE requires identifying the record by its list_id (for list entities) or txn_id (for transactions).

Update a Customer

UPDATE customers
SET phone = '555-0200', email = 'newemail@acme.com'
WHERE list_id = '80000001-1234567890';

Update an Invoice

UPDATE invoices
SET memo = 'Updated memo', is_pending = false
WHERE txn_id = '12345-1234567890';

QuickBooks requires the current edit_sequence to update a record. Syntra automatically reads the current edit_sequence before issuing the update, so you do not need to pass it explicitly.

DELETE: Removing Records

DELETE also requires list_id or txn_id:

DELETE FROM customers
WHERE list_id = '80000001-1234567890';

For transactions, QuickBooks distinguishes between deleting and voiding. A DELETE removes the transaction entirely. To void a transaction instead (preserving the record with a zero amount), use the special void command:

CALL qb_void('Invoice', '12345-1234567890');

See Special Commands for more details on voiding.

Supported Entities for Write Operations

Headers that support INSERT and UPDATE:

Entity category	Examples
Customer-side lists	customers, customer_msgs, customer_types, sales_reps, sales_tax_codes, price_levels, standard_terms, date_driven_terms
Vendor-side lists	vendors, vendor_types, ship_methods, payment_methods, billing_rates
Accounts and classes	accounts, classes, currencies, other_names, job_types, sales_tax_return_lines
Items	item_services, item_inventories, item_inventory_assemblies, item_non_inventories, item_other_charges, item_fixed_assets, item_discounts, item_payments, item_sales_taxes, item_sales_tax_groups, item_subtotals, item_groups
Employees	employees (DELETE not supported)
Transactions	invoices, bills, sales_receipts, credit_memos, estimates, sales_orders, purchase_orders, journal_entries, inventory_adjustments, build_assemblies, checks, credit_card_charges, credit_card_credits, deposits, transfers, time_trackings, vendor_credits, item_receipts, receive_payments, bill_payment_checks, bill_payment_credit_cards, sales_tax_payment_checks, charges

Entities that are read-only (no INSERT/UPDATE/DELETE):

account_tax_line_infos, alerts, bill_to_pays, companies, company_activities, data_ext_defs, employee_defaults, form1099_category_account_mappings, hosts, item_assemblies_can_builds, item_sites, list_deleteds, paychecks, payroll_item_non_wages, payroll_last_periods, payroll_liability_checks, preferences, receive_payment_to_deposits, sales_tax_payables, sales_tax_returns, templates, transactions, txn_deleteds, and every line table (invoice_lines, expense_lines, etc.).

Some reference lists are create-only (INSERT + DELETE but no UPDATE): ar_refund_credit_cards, billing_rates, customer_msgs, customer_types, date_driven_terms, job_types, payment_methods, sales_tax_return_lines, ship_methods, standard_terms, unit_of_measure_sets, vendor_types, vehicle_mileages.

To check any specific table, use DESCRIBE TABLE <name> or the describe_table MCP tool — the response includes supports_insert, supports_update, and supports_delete flags that reflect the live capabilities for that entity.

Important Notes

• QuickBooks must be running: Write operations require an active QuickBooks Desktop instance with the company file open.
• Pro plan required: Write operations are a Pro plan feature. Standard plan connections return an error on INSERT, UPDATE, DELETE, and voids.
• No batch inserts: Each INSERT statement creates one record. To create multiple records, execute multiple INSERT statements. The MCP insert_record tool accepts an array of parents for batch creation.
• Transaction boundaries: Each statement is auto-committed. There is no multi-statement transaction support.
• Required fields: QuickBooks enforces its own required fields. If a required field is missing, the operation fails with a descriptive error.
• Cache refresh: After a write operation, the local cache is automatically updated with the new data.
• Edit sequence: Updates require the current edit_sequence value. Syntra reads it for you automatically.