If you need to create a customer, you can use a format similar to this:

For USA Editions of QuickBooks:
INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode")
values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

For Non-USA Editions of QuickBooks:
INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressCounty","BillAddressPostalcode")
values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 Smith
St.','Lysterfiled','VIC','3156')

For jobs, add ParentRefListID or ParentRefFullName of the Jobs parent record.

Note: Name must be unique across Customer, Employee, Vendor, and OtherName. You can query the Entity to verify if a name already exists.

I have inserted a new customer successfully. The customer shows in QuickBooks, but when I do a query in the VB Demo application the customer record isn't there. What causes that to happen and how do I fix it?

This occurs when your computer's time setting has been changed or when using a QuickBooks sample file. When this happens you can resync your optimized customer table by running:-

sp_optimizefullsync customer

or you can by-pass the optimized table by doing:-

select * from customer unoptimized

That's nice but what about if I wanted to create a new Customer including custom fields with values in them?

While you can use the CustomField table, here's the more straightforward method of creating a new customer and updating a custom field. Using the Sample Rock Castle Construction example in QuickBooks 2006, if you do a:

sp_columns customer

You will see that the example has the following Custom Fields: CustomFieldBDay, CustomFieldContract and CustomFieldSpousesName. The only limitation is that we can't insert them in an insert, but updates are not a problem!

To create a new customer, you can use a format similar to this:

INSERT INTO "customer" ("Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode")
values('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')

To find the ListID of our new Customer just created we run the stored procedure:

SP_LASTINSERTID customer

Now that we have the ListID for our new customer, we can simply update the Custom Field Contract with the contract number by doing:

Update Customer Set CustomFieldContract='QODBCJOB1' where ListID='AB0000-1197756245'

And there you have it! To check that everything actually worked just do:

Select ListID, Name, FullName, CustomFieldContract from Customer where ListID='AB0000-1197756245'

Still don't believe it's that simple? Let's look inside QuickBooks ..... Wow!

For QuickBooks 2007 USA Editions you now have access to 5th address line (Notes). The following select statement shows all the default columns (except for any custom columns):

SELECT "ListID","TimeCreated","TimeModified","EditSequence","Name","FullName","IsActive","ParentRefListID",
"ParentRefFullName","Sublevel","CompanyName","Salutation","FirstName","MiddleName","LastName",
"BillAddressAddr1","BillAddressAddr2","BillAddressAddr3","BillAddressAddr4","BillAddressAddr5",
"BillAddressCity","BillAddressState","BillAddressPostalCode","BillAddressCountry","BillAddressNote",
"BillAddressBlockAddr1","BillAddressBlockAddr2","BillAddressBlockAddr3","BillAddressBlockAddr4",
"BillAddressBlockAddr5","ShipAddressAddr1","ShipAddressAddr2","ShipAddressAddr3","ShipAddressAddr4",
"ShipAddressAddr5","ShipAddressCity","ShipAddressState","ShipAddressPostalCode","ShipAddressCountry",
"ShipAddressNote","ShipAddressBlockAddr1","ShipAddressBlockAddr2","ShipAddressBlockAddr3",
"ShipAddressBlockAddr4","ShipAddressBlockAddr5","Phone","AltPhone","Fax","Contact",
"AltContact","CustomerTypeRefListID","CustomerTypeRefFullName","TermsRefListID","TermsRefFullName",
"SalesRepRefListID","SalesRepRefFullName","Balance","TotalBalance","OpenBalance","OpenBalanceDate",
"SalesTaxCodeRefListID","SalesTaxCodeRefFullName","ItemSalesTaxRefListID","ItemSalesTaxRefFullName",
"ResaleNumber","AccountNumber","CreditLimit","PreferredPaymentMethodRefListID",
"PreferredPaymentMethodRefFullName","CreditCardInfoCreditCardNumber","CreditCardInfoExpirationMonth",
"CreditCardInfoExpirationYear","CreditCardInfoNameOnCard","CreditCardInfoCreditCardAddress",
"CreditCardInfoCreditCardPostalCode","JobStatus","JobStartDate","JobProjectedEndDate","JobEndDate",
"JobDesc","JobTypeRefListID","JobTypeRefFullName","PriceLevelRefListID","PriceLevelRefFullName"
FROM "Customer"