Clarity Service Management

CA SDM Cleaning duplicate contacts - MS SQL 

12-16-2015 11:59 AM

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

Statistics
0 Favorited
2 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

03-07-2016 04:25 PM

Hi,

Thanks for providing  input to the community but I'm sorry to don't like your approach using pdm_load and also mostly as you don't take care of any of the foreign table like usp_contact table  and others LREL and or BREL/QREL tables resulting of orphan records and bad data consistency in your mdb.

Not something I will  recommend.

Thanks

/J

01-05-2016 12:46 PM

Obrigado, útil.

Related Entries and Links

No Related Resource entered.