here my code it removes all the duplicate contactname and some of the clientnames, I should have 700 records I have 199 shows. I would like to get rid of cursors, they are very slow.how can i use while loop instead cursor? DECLARE @TestFolderPath VarChar(255) SET @TestFolderPath = 'SQL1_Contacts' DECLARE @Contactname varchar(50), @contmethodvalue varchar(100), @contmethodtype VarChar(15), @Phone VarChar(50), @Email VarChar(50), @billingaddress1 VarChar(50), @billingCity VarChar(50), @billingState VarChar(50), @billingZip VarChar(50), @groupid varchar(10), @clientname varchar(50), @status varchar(10) DECLARE Inserted_Cursor CURSOR FORWARD_ONLY READ_ONLY FOR select c.clientname,a.contactname,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Phone' ORDER BY comethodvalue) AS Phone,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Email' ORDER BY comethodvalue) AS Email, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip From localtest.dbo.contacts as a , localtest.dbo.clients as c where a.clientid = c.clientid and c.groupid = 'RERE' AND a.stat IS NULL OR a.stat <> 'inactive' and a.contactname <> '' OPEN Inserted_Cursor FETCH NEXT FROM Inserted_Cursor INTO @clientname, @contactname, @Phone, @Email, @billingaddress1, @billingCity, @billingState, @billingZip WHILE @@FETCH_STATUS = 0 -- see if there are any more rows-- BEGIN EXEC master.dbo.xp_mycontacts @FolderPath = @TestFolderPath, @matchFileAs = @contactname, @firstname = @clientname, @Phone = @Phone, @Email = @Email, @Street = @billingaddress1, @City = @billingCity, @State = @billingState, @Zip = @billingZip FETCH NEXT FROM Inserted_Cursor INTO @contactname, @clientname, @Phone, @Email, @billingaddress1, @billingCity, @billingstate, @billingZip END CLOSE Inserted_Cursor; DEALLOCATE Inserted_Cursor
Post the table structure with some sample data and expected result would be helpful To see the duplicates in the clientname Select ClientName,count(*) from yourTable group by ClientName having count(*)>1 Madhivanan Failing to plan is Planning to fail