Firstly thanks a lot Anton & everyone !!!
Actually i'm not active captcha in my site that cause lot of junk records (not valid users) added. I don't want to delete Guest records.
e.g. some emails -[email protected][email protected][email protected][email protected]
...etc. thousands of records in customer table.
i'm trying follows SP & this can be work for me...
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[DeleteJunkCustomer]
@TotalRecordsDeleted int = null OUTPUT
CREATE TABLE #tmp_customers (CustomerId int)
INSERT #tmp_customers (CustomerId)
SELECT [Id] FROM [Customer] c with (NOLOCK)
WHERE Email like @JunkCustomerPattern
((NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci inner join [Customer] on sci.[CustomerId]=c.[Id])))
(NOT EXISTS(SELECT 1 FROM [Order] o with (NOLOCK) inner join [Customer] with (NOLOCK) on o.[CustomerId]=c.[Id]))
----delete junk cust
WHERE [Id] IN (SELECT [CustomerID] FROM #tmp_customers)
WHERE ([EntityID] IN (SELECT [CustomerID] FROM #tmp_customers))
([KeyGroup] = N'Customer')
SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_customers
DROP TABLE #tmp_customers