如何在SQL Server中迁移数据? [英] How to migrate data in sql server?

问题描述

我有一个表，它是tblCustomerOld，其中包含35万个记录.从那里重复1.57十万.

执行查询20小时后，无论何时执行查询，删除显示错误"缓冲池中的内存不足"的重复项.

有什么办法可以迁移数据.我的表结构如下.我必须将主键设置为"CUSTID"字段.

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

该查询将删除除(A = 1)以外的所有内容
但是我的要求是删除除(A =最多一个)以外的所有内容.

请帮我.


bye

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

Plese help me.


bye

推荐答案

建议:如果内存不足，请尝试在两者之间进行提交，或者通过提供任何类型的记录重新整理来批量执行查询状况. :)希望对您有帮助

关于如何删除重复项，有一篇很好的文章，

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

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 :)

您需要在删除一定数量后压缩日志文件.

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

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.

您是否考虑过创建新表，并且从旧表的DISTINCT值填充它?然后，您可以删除原始文件.

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.

SELECT DISTINCT column1, column2 INTO newTable FROM OldTable

这篇关于如何在SQL Server中迁移数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！