Inserting Customers into HQ using SQL

We are writing an integration to RMS HQ Customers from an application called Campus View. I'm trying to write an insert statement to insert into the Customer table. I first tried inserting a record without explicitly setting the ID or HQID fields in the script to see if SQL would auto-generate these key fields for me. I received an error that null values weren't allowed.

I tried manually creating a customer to see what ID values it would assign. Rather than starting at 1 and auto incrementing it appears to be choosing very long numbers to assign as the ID fields. For example the first ID for the first customer was 1073741825 and the second was 1073741825.

I did a SQL trace and found that when manually creating a customer in HQ, HQ first inserts a customer record with zero for the ID and HQID field, then it selects @@identity and then does an update on the customer record to set the ID and HQID values. My guess is that it auto-Generates the ID and HQID by running the customers Account Number through some kind of algorithm. I know I've seen other 3rd parties that insert records into the HQ Customer table, so there must be some way to do this.

My question is, if I want to insert a customer into the HQ Customer table can I just insert any unique value into the ID field, or is there some more complicated method RMS is using to generate these ID values that my SQL script needs to follow? How do I Generate the correct ID value?

Reply to
Tdahnert
Loading thread data ...

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.