How to migrate data in sql server?

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 15-Nov-11 0:41am

purnananda behera

Updated 15-Nov-11 17:08pm

v4

Add a Solution

Comments

Mehdi Gholam 15-Nov-11 6:44am

How are you comparing for duplicates?

purnananda behera 15-Nov-11 7:18am

When I found duplicate records then by through the row_number I am selecting the maximum one.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pandya Anil · Answer 1 · 2011-11-15T01:04:00

a suggestion that, if you are falling short of memory.. try commiting in between.. or execute your query in batches by providing any kind of record restrincting condition. :) hope it will help you

there is a good article on how to remove duplicates,

http://chiragrdarji.wordpress.com/2007/07/23/delete-single-row-from-duplicate-rows-in-sql-server-2005-and-2000/[^]

mark as answer if helps you, it motivates :)

deepakdynamite · Answer 2 · 2011-11-15T02:07:00

Solution 3

You need to compress Log files after certain amount of Deletions.

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/[^]

Also don't delete whole records at one time... Delete some of them then Truncate Log files and again fire query to delete them.

Posted 15-Nov-11 2:07am

deepakdynamite

OriginalGriff · Answer 3 · 2011-11-15T01:13:00

Solution 2

Have you considered creating a new table, and filling it from the DISTINCT values from the old table? You could then just delete the original.

SQL

SELECT DISTINCT column1, column2 INTO newTable FROM OldTable

Posted 15-Nov-11 1:13am

OriginalGriff