Need help on a dedupe query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help on a dedupe query

Author

Topic

Robert London
Starting Member

26 Posts

Posted - 2010-08-15 : 12:07:10

Hi,

I'm fairly new to SQL and wondered if you would help me with this deduping query, it seems to take in inordinate amount of time to run?

drop procedure DM_Phone_Dedupe_2
go
create procedure DM_Phone_Dedupe_2

as

begin

declare @ProcessName varchar(100),
@Progress varchar(200)

set nocount on

set @ProcessName = 'DM_Phone_Dedupe_2'

set @Progress = 'Started...'
exec DM_Report_Progress @ProcessName, @Progress

/****************************************************************************************************************/
declare @DM_TableCnt1 int,
@DM_PhoneNumber varchar(7999),
@SQL varchar(7999),
@DM_PhoneURN varchar(10)

set @Progress = 'Running 1st loop...'
exec DM_Report_Progress @ProcessName, @Progress

select @DM_TableCnt1 = COUNT(distinct DM_PhoneURN) from DM_Phone_Dedupe_Working_tmp (nolock)where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1
--set @DM_TableCnt1 = 5
while @DM_TableCnt1 <> 0
begin

select top 1 @DM_PhoneNumber = DM_PhoneNumber from DM_Phone_Dedupe_Working_tmp (nolock) where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1 -- <> '' AND DM_PhoneNumber <> '0' -- is not null
select top 1 @DM_PhoneURN = DM_PhoneURN from DM_Phone_Dedupe_Working_2 (nolock) where DM_PhoneNumber = @DM_PhoneNumber

--select 'no ' + @DM_PhoneNumber
--select 'urn '+ @DM_PhoneURN

set @Progress = 'Updating Daytime Phone...'
exec DM_Report_Progress @ProcessName, @Progress

set @SQL = 'insert into DM_AllData_Master_Ranked_Tel (DM_PhoneURN, DM_FileURN, DM_TableSRN, DM_PhoneNumber) ' +
' select '''+@DM_PhoneURN+ ''',a.DM_FileURN, a.DM_TableSRN,'''+@DM_PhoneNumber+''''+
' from DM_AllData_Master_Ranked (nolock) a left join DM_AllData_Master_Ranked_Tel b '+
' on a.DM_FileURN = b.DM_FileURN and a.DM_TableSRN = b.DM_TableSRN ' +
'where a.DM_FileURN <> ''LL_124'' and b.DM_TableSRN is null and (a.DaytimePhone = ''' +@DM_PhoneNumber+ '''' +
' or a.EveningPhone = ''' +@DM_PhoneNumber+ '''' +
' or a.MobileNo = ''' +@DM_PhoneNumber+ '''' +
'or a.Telephone = ''' +@DM_PhoneNumber+ '''' +
'or a.Telephone2 = ''' +@DM_PhoneNumber+ '''' +
'or a.Telephone3 = ''' +@DM_PhoneNumber+ '''' + ')'
exec(@SQL)
--print @SQL

set @Progress = 'Deleting from DM_Phone_Dedupe_Working_tmp...'
exec DM_Report_Progress @ProcessName, @Progress

set @SQL = 'delete from DM_Phone_Dedupe_Working_tmp where DM_PhoneNumber = '''+@DM_PhoneNumber+''''
exec (@SQL)

-- select @DM_TableCnt1 = COUNT(distinct DM_PhoneURN) from DM_Phone_Dedupe_Working_tmp (nolock) where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1

set @DM_TableCnt1 = @DM_TableCnt1 - 1
end

/****************************************************************************************************************/
/*
set @Progress = 'update blank phone urn...'
exec DM_Report_Progress @ProcessName, @Progress

-- need to give blank phone number a urn
update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.DaytimePhone, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null

update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.EveningPhone, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null

update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.MobileNo, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null

update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.Telephone, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null

update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.Telephone2, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null

update a
set a.DM_PhoneURN = b.DM_PhoneURN
from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
where replace(a.Telephone3, ' ', '') = b.DM_PhoneNumber and
a.DM_PhoneURN is null
*/
/****************************************************************************************************************/
/*
set nocount on

set @Progress = 'Running loop...'
exec DM_Report_Progress @ProcessName, @Progress

declare @DM_TableCnt int,
@TableName varchar(200),
@TableName2 varchar(200)

--select @DM_TableCnt = COUNT(distinct DM_TableName) from tmp_Address_Dedupe_Stage1
select @DM_TableCnt = COUNT(distinct TableName) from DM_TableLog where Ph_Processed is null
--select top 1 @TableName = TableName from tmp_DM_TableLog where Processed is null order by TableName

while @DM_TableCnt <> 0
begin
print @DM_TableCnt
select top 1 @TableName = DM_TableName from tmp_Phone_Dedupe_Stage1 order by DM_TableName

set @Progress = 'Processing ' +@TableName
exec DM_Report_Progress @ProcessName, @Progress

set @Progress = 'Updating DM_AllData_Master_Ranked...'
exec DM_Report_Progress @ProcessName, @Progress

set @SQL = 'update a
set a.DM_PhoneURN = b.DM_PhoneURN
from DM_AllData_Master_Ranked a, tmp_Phone_Dedupe_Stage1 b
where a.DM_TableName = b.DM_TableName and
a.DM_TableSRN = b.DM_TableSRN and
a.DM_PhoneURN is null and
a.DM_TableName = '''+@TableName+''' '
exec (@SQL)

set @Progress = 'Deleting from tmp_Phone_Dedupe_Stage1 '
exec DM_Report_Progress @ProcessName, @Progress

set @SQL = 'delete from tmp_Phone_Dedupe_Stage1 where DM_TableName = ''' +@TableName+''''
exec (@SQL)

set @TableName2 = replace(replace(@TableName, '[',''),']','')

set @Progress = 'Updating DM_TableLog...'
exec DM_Report_Progress @ProcessName, @Progress

set @SQL = 'update a
set a.Ph_Processed = ''Y''
from DM_TableLog a
where a.TableName = '''+@TableName2+''' '
exec (@SQL)

set @DM_TableCnt = @DM_TableCnt - 1

end

*/
set nocount off

/***************************************************************************************************************************************/

set @Progress = 'Finished.'
exec DM_Report_Progress @ProcessName, @Progress

end

Robert London
Starting Member

26 Posts

Posted - 2010-08-15 : 14:02:13

Can nobody help? :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:38:08

Why are you using dynamic sql?

N 56°04'39.26"
E 12°55'05.63"

Robert London
Starting Member

26 Posts

Posted - 2010-08-16 : 06:54:17

This query is to dedupe a table containing telephone numbers, written by our developer and I am wanting to see if it is a efficient as it can be

Robert London
Starting Member

26 Posts

Posted - 2010-08-16 : 11:45:42

60+ reads and nobody can help? :(

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-16 : 11:58:25

sure...but you need to understand something...

Wold you want to read through that mess?

You say you want to remove duplicates...

Fine

Are the Duplicates in 1 table?

post the DDL of the table (CREAT TABLE myTable99 Col1 int, Col2...)

It would also help if you showed us what the duplicates looked like ( and non dups as well), like DML

INSERT INTO myTable99(Collist)
SELECT 1,'mydata',ect UNION ALL
SELECT 1,'mydata',ect UNION ALL
..ect

And tell us what yo uwant to do with the dups

Keep 1, get rid of all...and the rules on how to do it...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources