Say you have many customers who place orders to your company but each customer has a different order format for placing an order. They send the orders in a csv or excel format. The data is uploaded to a SQL database. (The uploading of the data to SQL server is not the topic here.) The data in the database is stored as tables from each customer’s order file name.
Each table will have a configuration in a configuration table that will determine what fields to take to build a final stage table to be uploaded to an ERP solution. Steps in building the API.
1. Create a stored procedure to validate the fields. Example: Check the fields that are in the configuration file exist in the table. Then check the data type to see if you need to convert it to the correct data type or not. Example: A numeric value in the table may have a data type of varchar. You need to convert it to a numeric type.
2. Then create stored procedures to build the data to the final tables. This could be get customer information. Get ERP codes to build the orders. Then build validation of the orders to the final tables. Send email to recipient. 3. Then set a schedule to process the data. This concept was used to build a API for a client.