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'.