July 31, 2012 at 1:13 pm
Hi all,
Problem:
we receive several Excel files each week from different customers that we import into MSSQL for processing.
Processing, in this context, means I check the data to make sure there are no missing columns and I format it per the customers requirements. There are a lot of other things that happen to the data but it is not relevant to my question.
I have been processing these files on an individual basis and I keep the data in their own table based on the customers name.
Now requirements have changed, as they often do, and the people in charge want me to create a "Master" table that holds all records from all files that we have received and will receive in the future.
Here is my question.
I need a unique identifier in the master table that can tell me who the customer is that the record belongs to.
I would prefer that the identifier be alpha numeric and increment automatically.
My initial thinking is I would create a look up table with the customer's name and a prefix consisting of a few letters that would uniquely identify the customer.
The challenge I am having is how do I automatically increment this alpha numeric identifier?
Or if someone has a better approach I certainly would like to hear it.
Thanks in advance for your help.
Gary
July 31, 2012 at 1:32 pm
there are few ways you can do this , the first thing that comes to mind is to identify a format such as customername + timestamp in yyyymmdd format but this only works if you load data once a day per customer. the master table would contain the last successful Id and then simply add a new column to the staging table and run an update statement on the batch for each customer. Of couse you error logic needs to be able to handle partial loads etc
July 31, 2012 at 2:20 pm
similar to what Jayanth suggests, something like
customer_number
+ CONVERT(varchar,row_number()
OVER (partition by customer_number
ORDER BY customer_number)
)
would work as well.
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply