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(Id,Code,value,Row1) as ( Select Id,Code,value, 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(1,1), 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 (id,code,val,dcount) AS ( SELECT id, code, val , ROW_NUMBER() OVER (PARTITION BY code,val 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(1,1), 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

SQL Query - uniqueness in combination

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)