CA SDM Cleaning duplicate contacts - MS SQL

Document created by TMACUL Champion on Dec 16, 2015Last modified by TMACUL Champion on Mar 8, 2016
Version 2Show Document
  • View in full screen mode

It's a simple code to searching by duplicate contacts on this sample code was used e-mail to looking for duplicate registers therefore if, perhaps, do you need use another field like userid, easily you can change email_address to userid, and Everything That Happens Will Happen

 

WARNING: The best way to do: inactive contacts.

jmayer remember us ca_contact it's one table and the registers has many relations where can to result: orphan records.

 

 

01. TO run the script

DECLARE@emailVARCHAR(240)
,@uuidbinary(16)

 

 

DECLARE db_cursor CURSOR FOR 

   select   email_address

from ca_contact
where inactive = 0
group by email_address
having count(0) >1

 

 

DECLARE @tmp TABLE

(

  idint identity(1,1),
  uuidbinary(16),
  emailvarchar(240),
  loadvarchar(8000)

)

 

 

 

 

insert into @tmp (load) values ('TABLE  ca_contact')

insert into @tmp (load) values ('id email_address inactive')

 

 

 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @email  

 

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

select @uuid = max(contact_uuid) from ca_contact where email_address =  @email

 

 

--select @uuid,*  from ca_contact where email_address =  @email
if @uuid is not null insert into @tmp (uuid, email) values(@uuid, @email)

 

 

 

 

 

 

 

       FETCH NEXT FROM db_cursor INTO @email  

END  

 

 

CLOSE db_cursor  

DEALLOCATE db_cursor

 

 

 

update @tmp

set load = '{"' +  convert(varchar(32), uuid, 2) + '", "' + email + '", "1"}'

where uuid is not null

 

 

 

select load

from @tmp as tmp

order by id

 

02. Using exit - copy to text file called myupdate.txt - and save like UTF-8

 

03. Run command

 

pdm_load -v -u -f  myupdate.txt

Attachments

    Outcomes