1

I have a challenge in SQL Server that I would really appreciate your help on.

We have a unique value that we store in a string field for records that makes it easier for a user to recognize it e.g. ABC001 or XX01 or anything else that the user wants to enter.

We validate that the value is unique when a single record is updated for all records belonging to a particular context e.g. organization - so all records belonging to a particular organization must be unique.

The challenge is that we have a use case where many records are copied using a combination of a INSERT SELECT approach which is very efficient from one organization to another.

The problem is that when we copy e.g. 1000 records from one organization to another some of the unique id values might be the same and break the unique requirement.

So the question is how can we by using an INSERT SELECT approach modify the "unique id" value that clash on the fly such that if ID ABC01 already exists then save it as ABC01 (1), ABC01 (2) etc. until it is unique in the most efficient manner possible?

Thank you in advance for your reply.

  • You can use a trigger to modify data "on the fly". – Gordon Linoff Mar 22 '18 at 18:27
  • We are using Microsoft SQL server (and are on our way to migrate to SQL Azure). Thanks. –  Mar 23 '18 at 06:33
  • Not sure if it is possible for you but one approach would be to store the string part (organization) in one column and let the unique part be an int column with auto-increment and then concat the two whenever you're reading from the table. – Joakim Danielson Mar 23 '18 at 07:05

0 Answers0