How to migrate data in sql server?

See more:

SQL-Server

Hi,

I have a table that is tblCustomerOld which contains 35 lakhs records. From there 1.57 lakhs are duplicate.

Whenever I am executing the query to remove duplicate that showing the error "There is insufficient memory in buffer pool" after 20 hours of query execution.

Is there any way to migrate data. My table structure is below. I have to set primary key to "CUSTID" field.

CREATE TABLE [dbo].[tblCustomerOld](
	[PBN] [varchar](7) NULL,
	[ACCOUNT_NO] [varchar](15) NULL,
	[CUSTOMER_NAME] [varchar](100) NULL,
	[CUSTOMER_PRIORITY] [varchar](150) NULL,
	[ADDRESS1] [varchar](200) NULL,
	[ADDRESS2] [varchar](200) NULL,
	[ADDRESS3] [varchar](200) NULL,
	[CITY] [varchar](200) NULL,
	[STATE] [varchar](200) NULL,
	[PINCODE] [varchar](200) NULL,
	[CUST_TYPE] [varchar](15) NULL,
	[PBNSTATUS] [varchar](10) NULL,
	[CUSTID] [varchar](6) NOT NULL,
	[FLAG] [varchar](1) NULL
) 

Please give me the solution.

I got a solution :

With Test AS (SELECT ROW_NUMBER () OVER ( PARTITION BY custid ORDER BY custid desc) AS A FROM tblCustomerOld ) DELETE FROM Test WHERE A > 1

This query is deleting all except (A=1)
But My requirement is to delete all except (A = maximum one).

Plese help me.


bye