making a match key and deduping

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

making a match key and deduping

Author

Topic

tryharder
Starting Member

17 Posts

Posted - 2013-05-24 : 04:03:58

HI,

I ned to create a match key based on the following,
title, initial, 6 letters of the surname, 6 letters of address line 1 and the post code.
I then need to internally dedupe the file based on a hierarcy of the files marked 1-77 starting at 77 first.

could you let me know how to build the match key and run the dedupe?

thanks

SP

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 04:41:48

SELECT Title + LEFT(Surname, 6) +LEFT(Address_Line1, 6) + PostCode AS MatchKey
FROM TableName;

>> then need to internally dedupe the file based on a hierarcy of the files marked 1-77 starting at 77 first.
I'm not understanding this point

--
Chandu

tryharder
Starting Member

17 Posts

Posted - 2013-05-24 : 04:57:31

thanks for the match key code. regarding the dedupe, I need to remove duplicate records but in an order so any records with a code of 77 ( this just relates to when the file was uplaoded )will be matched against all the other files and any duplicates removed you then do this for the remaining 76 files

SP

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 05:17:53

--Example for dedupe is as follows:
DELETE duplicates
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, salary,dept ORDER BY empno) cnt
FROM tblEmp) duplicates
WHERE duplicates.Cnt > 1

Here tblEmp is TableName which has columns firstname, lastname, salary, dept and empno..

--
Chandu

tryharder
Starting Member

17 Posts

Posted - 2013-05-24 : 05:26:52

thanks will give it a try, appreciate it

SP

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-24 : 05:51:59

quote:
Originally posted by tryharder

thanks will give it a try, appreciate it
SP

Welcome.. Let us know further progress

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources