Share via


SQL Server: INSERT to Remove Duplicate Rows

First, we will create #test table with a unique index and insert duplicate rows.

CREATE  TABLE #test
(name varchar(15)  UNIQUE,  -- Define a UNIQUE constraint
 profession varchar(25),
 salary int  DEFAULT 0,
 )
 
INSERT #test VALUES ('Paul Raj','Actor',-0045)
 
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
 
-- Now attempt to insert a duplicate value
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
  
(1 row(s) affected)
 
(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 12
Violation of UNIQUE  KEY constraint 'UQ__#test_____72E12F1B117F9D94'. Cannot  insert duplicate key  in object 'dbo.#test'. The duplicate key value is (Julio Sezar).
The statement has been terminated.

SQL server will give an error message:

'Cannot ``insert duplicate ``key in object'

and eats request.

How to add data? Delete duplicate rows? And if million rows in inserting data?

It was a great problem in our query.

For these problems, the power Microsoft SQL Server language gives us an awesome option.

IGNORE_DUP_KEY  for deleting duplicate rows with inserting process for testing this option.

First, we will create our #test table  again with unique index for inserted and insert table duplicate rows but here we will use:

UNIQUE INDEX  WITH IGNORE_DUP_KEY

CREATE  TABLE #test
(name varchar(15)  UNIQUE,  -- Define a UNIQUE constraint
 profession varchar(25),
 salary int  DEFAULT 0,
) 
 CREATE UNIQUE  INDEX removedups ON #test (name,profession,salary)
WITH IGNORE_DUP_KEY 
 
INSERT #test VALUES ('Paul Raj','Actor',-0045)
 
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
 
-- Now attempt to insert a duplicate value
INSERT #test VALUES ('Julio Sezar','Teacher',-0055)
 
select *from  #test
drop  table #test
 
Duplicate key was ignored.
 
 
name                        profession                salary
--------------- ------------------------- -----------
Julio Sezar     Teacher                   -55
Paul Raj        Actor                     -45
 
(2 row(s) affected)

 Inserting was working successfully and in the resulting message gives us ' Duplicate ``key was ignored'.