Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Constraint to modify value if not unique

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
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
 
How about?

Code:
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
 
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
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top