SQL - Autogenarating Customer IDs
I have been swamped lately working on 3 different projects, and it's been difficult to get back to blogging as often as I am used to. However, this week I found myself with an interesting situation where my client needed to import a customer master list into GP, but was migrating from a system that autoassigned an internal key to each customer record. My client wanted to move away from this "unique internal number" to an alphanumeric nomenclation similar to that of Fabrikam, for example, Aaron Fitz would have a customer ID of AARONFIT0001. They thought it was a very intuitive way of identifying customers and that it would serve the AR staff well. The customer data was staged in a SQL database and the goal was to create a query that will assign these customer IDs based on the customer name. So, here is the solution: AutoCustomerID.sql -- Created by Mariano Gomez, MVP SELECT -- evaluates the 9 first characters of the customer name and removes any blanks -- other characte