SQL Query - uniqueness in combination

Question

Hi all I have a table as - ID Code Value 1 101 abc 2 101 def 3 201 abc 4 101 def Now In this table I need to check the uniqueness in combination - 101 - abc ---> allowed. 101 - def ---> allowed. 101 - def ->> NOT ALLOWED.... Combination 101 - def exists. Similarly Combination 101 - abc must not be allowed next time. How to check this combination uniqueness in SQL Server ?? Please help !!

Pankaj Kumar Choudhary · Answer

hello @Riddhi use this code for your second query With CTE(IdCodevalueRow1) as ( Select IdCodevalue ROW_NUMBER() Over(partition by Code  Value order By Code) as Row1 from Table_name ) Select * from CTE where Row1>1

sudipta sanyal · Answer

How to fetch the combination duplicates records from the table CREATE table #temp_data (id INT IDENTITY(11) code INT val NVARCHAR(20)) INSERT INTO #temp_data VALUES (101'abc') INSERT INTO #temp_data VALUES (101'def') INSERT INTO #temp_data VALUES (201'abc') INSERT INTO #temp_data VALUES (101'abc') INSERT INTO #temp_data VALUES (101'abc') INSERT INTO #temp_data VALUES (201'abc') ;WITH CTE (idcodevaldcount) AS ( SELECT id code val  ROW_NUMBER() OVER (PARTITION BY codeval ORDER BY code) AS dcount FROM #temp_data ) SELECT id code val FROM CTE WHERE dcount > 1; SELECT * FROM #temp_data DROP TABLE #temp_data Hello @riddhi Please Mark as Accepted Answer if it helps you.

Pankaj Kumar Choudhary · Answer

Hello @riddhi you can use this code for your first query Create Trigger Insert_ On Table_name Instead Of Insert as Begin Declare @Code int Declare @value nvarchar(max) select @Code= tab.Code from inserted tab select @value= tab.value from inserted tab IF NOT EXISTS (SELECT * FROM table_name WHERE code = @Code AND value = @value) BEGIN INSERT INTO Table_name VALUES (@Code@value) END End

Riddhi Valecha · Answer

Hi.. Thanks a ton.... Will do this logic now ! And how to fetch the combination duplicates records from the table (with more than 5000 records ) ?? Please guide !

sudipta sanyal · Answer

CREATE table #temp_data (id INT IDENTITY(11) code INT val NVARCHAR(20)) INSERT INTO #temp_data VALUES (101'abc') INSERT INTO #temp_data VALUES (101'def') INSERT INTO #temp_data VALUES (201'abc') IF NOT EXISTS (SELECT * FROM #temp_data WHERE code = 101 AND val = 'abc') BEGIN INSERT INTO #temp_data VALUES (101'abc') END SELECT * FROM #temp_data DROP TABLE #temp_data