July 25, 2017 at 11:50 am
hi,
This is my sample script to identify and delete the duplicate in a transaction. Please give suggestions if there is better way to improve the below script ? I wanted to do this in transaction.
/*** here is the script****/
create table Employee
(
Eno int identity(1,1),
Ename varchar(5),
ECode varchar(10)
)
insert into EMPLoyee
select 'AA','Management'
union all
select 'AA','Management'
union all
select 'BB','IT'
union all
select 'CC','IT'
BEGIN TRANSACTION
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE [#temp]
END
;WITH CTE AS(
SELECT ENO,[Ename]
,[ECode]
, RN = ROW_NUMBER()OVER(PARTITION BY [Ename]
,[ECode] ORDER BY [ENO])
FROM Employee
)
SELECT [ENO],[Ename]
,[ECode]
into #temp
FROM CTE where Rn>=2
ORDER BY ENO,[Ename]
,[ECode]
If (Select count(*) from #temp )>=1
BEGIN
DELETE FROM [M]
FROM Employee [M]
JOIN #temp [T] ON [m].Eno=[t].Eno
END
COMMIT
July 25, 2017 at 12:59 pm
komal145 - Tuesday, July 25, 2017 11:50 AMhi,
This is my sample script to identify and delete the duplicate in a transaction. Please give suggestions if there is better way to improve the below script ? I wanted to do this in transaction./*** here is the script****/
create table Employee
(
Eno int identity(1,1),
Ename varchar(5),
ECode varchar(10)
)insert into EMPLoyee
select 'AA','Management'
union all
select 'AA','Management'
union all
select 'BB','IT'
union all
select 'CC','IT'BEGIN TRANSACTION
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE [#temp]
END
;WITH CTE AS(
SELECT ENO,[Ename]
,[ECode]
, RN = ROW_NUMBER()OVER(PARTITION BY [Ename]
,[ECode] ORDER BY [ENO])
FROM Employee
)
SELECT [ENO],[Ename]
,[ECode]
into #temp
FROM CTE where Rn>=2
ORDER BY ENO,[Ename]
,[ECode]If (Select count(*) from #temp )>=1
BEGIN
DELETE FROM [M]
FROM Employee [M]
JOIN #temp [T] ON [m].Eno=[t].Eno
END
COMMIT
Skip the temp table. It's extra work. Delete directly from the CTE.
;WITH CTE AS(
SELECT ENO,[Ename]
, [ECode]
, RN = ROW_NUMBER()OVER(PARTITION BY [Ename], [ECode] ORDER BY [ENO])
FROM Employee
)
DELETE CTE where Rn>=2
And that way, there is only one statement, so you don't need an explicit transaction.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2017 at 1:02 pm
drew.allen - Tuesday, July 25, 2017 12:59 PMkomal145 - Tuesday, July 25, 2017 11:50 AMhi,
This is my sample script to identify and delete the duplicate in a transaction. Please give suggestions if there is better way to improve the below script ? I wanted to do this in transaction./*** here is the script****/
create table Employee
(
Eno int identity(1,1),
Ename varchar(5),
ECode varchar(10)
)insert into EMPLoyee
select 'AA','Management'
union all
select 'AA','Management'
union all
select 'BB','IT'
union all
select 'CC','IT'BEGIN TRANSACTION
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE [#temp]
END
;WITH CTE AS(
SELECT ENO,[Ename]
,[ECode]
, RN = ROW_NUMBER()OVER(PARTITION BY [Ename]
,[ECode] ORDER BY [ENO])
FROM Employee
)
SELECT [ENO],[Ename]
,[ECode]
into #temp
FROM CTE where Rn>=2
ORDER BY ENO,[Ename]
,[ECode]If (Select count(*) from #temp )>=1
BEGIN
DELETE FROM [M]
FROM Employee [M]
JOIN #temp [T] ON [m].Eno=[t].Eno
END
COMMITSkip the temp table. It's extra work. Delete directly from the CTE.
;WITH CTE AS(
SELECT ENO,[Ename]
, [ECode]
, RN = ROW_NUMBER()OVER(PARTITION BY [Ename], [ECode] ORDER BY [ENO])
FROM Employee
)
DELETE CTE where Rn>=2
And that way, there is only one statement, so you don't need an explicit transaction.Drew
thing is need to identify dupes in one step and another step delete dupes in another step. Also need to do this in a transaction.
July 26, 2017 at 5:15 am
Any specific reason as to why you want to do it in 2 steps when you can do it in a single step as shown by Drew?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply