Not sure how I can do this in SQL Query. I can do it via vb.net, but not sure how simple it is to do it via SQL Query. I have an excel list that is given to me on a daily basis. I need to take this excel file and dump it into a temp table. Then take the prefix and look in the database to see what number is already being use. If a number is available, then I want it to append the availble number to the prefix.
Prefix ABCD is on the list. In the database table "Item" has a ton of parts that start with ABCD. All these item id are only 10 digits long. So we got the prefix and append any number with leading zeros to it. Example, item "ABCD000300" is the last number so this new item ID will be ABCD000301. If there's a skip number between ABCD000298 and ABCD000300, then ABCD000299 will be use.
So to clarify, I need to search through the table "Item" for like prefix. Then compare the number to see which number is available. If it's available, then use that and append leading zeros so that the new ID is 10 digits long. Any ideas? See screenshot.