Sage has a tool called Data Migrator which can import customers. It loads a tarCustomer record(s) from a staging table called stgCustomer. Do a quick search for stgCustomer. One of the stored procs will be the one you are looking for. There will be no documentation but it should be pretty easy to use.

As John points out, Sage 500 does have the Data Migrator tool for this purpose. The stored procedure it uses is spARapiCustomerIns. There isn't any "formal" documentation on this stored procedure, but if you review it and read the programmer notes you should get a pretty good idea of what is required to use it, its capabilities, and its limitations.

Thank you for replying!

I have been through the suggested tables and stored procedures.

However, the stored procedures are there to migrate records from "staging" or "temporary" tables and the migration is where the validations exist. I cannot find any procedures to create records in staging tables. So does that mean that I have to make stored procedure to insert records in all the staging tables that stores information about customer(eg. StgCustomer, StgCustAdd and may be more) and then migrate them or "am I missing something"?

You are correct, if you are not using Data Migrator, you will need to manually populate the staging tables yourself and then execute the stored procedure to create the customer records. There is no stored procedure to do this as the data could be from any source (website/webservice, delimited file, Excel, etc.). If you are using delimited files or Excel, you might find it easier to use Data Migrator from the Sage 500 client to process your data as it will populate the staging tables for you and execute the stored procedure.

If your requirements are different, you can use either temporary or the actual staging tables themselves to process data. There is a parameter @iUseStageTable that you can pass to the stored procedure to indicate if you are using temp tables or not. We do a lot of work with these APIs using SSIS and the first stage of the process is always populating the staging tables with the appropriate data with the second stage executing the API stored procedure.

Now that I get a clear idea about how to "create" a record in sage 500 through API, I'll appreciate your help in exploring the process of "updating" any record. Is it that I have to make my own stored procedure to validate data(using sage 500 stored procedures) and directly update the main tables like "tarCustomer, tarCustAddr, etc." or is this something that sage 500 already provides in the form of stored procedure api?

In the past (when I worked for a reseller) I directly updated the tables. I am not aware of an API to do updates. Sage sort of frowns on this though. Just an FYI.

Sage highly frowns upon direct updates to their tables. Your best bet is to use the API as includes validations necessary for creating new customers. As I mentioned before review the stored procedure to see what it actually does and what limitations it has. If necessary you can create your own modified version of this procedure to fill any gaps you find in what you need to accomplish. Simply script the stored procedure and add some identifier to the stored procedure name to identify it as yours. Do not modify the original as any updates from Sage will overwrite your modifications to it.

And I thought sage 100 had bad API. Sage 500 definitely is a winner!

Nonetheless, thanks a lot guys for helping me out.

Having worked with Sage 500 for many years, the current Data Migrator APIs are much better than the originals provided with the Sage 500 SDK and in my humble opinion are very versatile. You have to remember they were written for Data Migrator specifically and not 3rd party developers.

As a developer, I understand this and fill in any gaps to complete the solution the customer is looking for. As I mentioned we have built SSIS packages around these APIs that integrate to other systems, read XML files, and pull files from FTP sites, but we have also created WCF Web Services as well for some of these APIs that populate the staging tables and execute the API so other systems (Web sites, mobile applications, etc.) can import data to Sage 500 in real time.

I understand your frustration, but the API is already doing much of the work for you. As I recommended, you simply need to understand how the API is currently working and what gaps you might need to fill in. One way to do this is to simply build a simple T-SQL script that populates the staging tables, executes the API, and reads the results from tdmMigrationLogWrk to better understand what the API is doing based on the data you are providing it.

Good Luck