Constraint to modify value if not unique

[wbodger]

I have a process that does a bulk import where I build a field value given incoming values. Sometimes, ALL of the incoming values are the same, but they are not duplicates. Since this is a bulk import, I don't know of any way to really check if the field value I have built is being built the same for another record within that import. So, I am wondering about the possibility to have some kind of constraint or trigger on the insert? For instance, I have the records:

[pre]
AcmeTools 20000 1/15/2018 Credit 12345678
AcmeTools 20000 1/15/2018 Credit 12345678
[/pre]

When I build the field I would typically use something like 12345678Acmetools01152018$200000

However, I need the fields to be unique and this will not create a unique value. So, I would like to add an incremental value on to the end of the created value if it would be a duplicate, so 12345678Acmetools01152018$2000001, 12345678Acmetools01152018$2000002, etc. Is there a way to do this with a constraint or does this need to be a trigger? And I am trying to find an efficient method as this could be part of a daily import of up to 20000 records 7 days/week.

Thanks in advance for any help!
Willie

 

[DaveInIowa]

How about?

DECLARE @Things TABLE (Name VARCHAR(50), Number INT, SomeDate DATE, Type VARCHAR(16), BiggerNumber INT)
INSERT INTO @Things VALUES
('AcmeTools', 20000, '1/15/2018', 'Credit', 12345678),
('AcmeTools', 20000, '1/15/2018', 'Credit', 12345678),
('AcmeTools', 20000, '1/15/2018', 'Credit', 12345679)

;WITH t AS (
SELECT *,
	   COUNT(*) OVER (PARTITION BY Name, Number, SomeDate, Type, BiggerNumber) DupKeyValues,
	   ROW_NUMBER() OVER (PARTITION BY Name, Number, SomeDate, Type, BiggerNumber ORDER BY Name) DupKeyIndex
  FROM @Things
)

SELECT *,
	   CASE
	      WHEN DupKeyValues = 1 THEN ''
		  ELSE '$' + CAST(DupKeyValues AS VARCHAR) + RIGHT('000000' + CAST(DupKeyIndex AS VARCHAR), 6)
	   END KeyPostfix
  FROM t

 

[wbodger]

So, it seems like I will really need to use a trigger to achieve this, yes? I like the way you used row_number.
Thanks,
willie

 

[DaveInIowa]

I guess I'm not clear on when the unique suffix is to be generated; during bulk import or at a later date? Why not just use an identity value to make each row unique?