删除数据库重复数据一方法

最新推荐文章于 2024-11-29 10:36:48 发布

转载最新推荐文章于 2024-11-29 10:36:48 发布 · 112 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sendling/archive/2009/01/13/1374625.html

文章标签：

#数据库

今天遇到重复数据问题,我这里主要把主键自动增长去掉先,不然有这个"仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 '' 中为标识列指定显式值"问题。

创建表

Code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testchongfu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testchongfu]
GO

CREATE TABLE [dbo].[testchongfu] (
    [ID] INT  NOT NULL ,
    [Title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [getdate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[testchongfu] ADD
    CONSTRAINT [DF_testchongfu_getdate] DEFAULT (getdate()) FOR [getdate],
    CONSTRAINT [PK_testchongfu] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
GO

exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'testchongfu', N'column', N'getdate'

GO

插入数据

Code
insert into testchongfu(ID,Title)values(1,'sddsf')
insert into testchongfu(ID,Title)values(2,'sddsf')
insert into testchongfu(ID,Title)values(3,'23')
insert into testchongfu(ID,Title)values(4,'4324')

创建临时表

Code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testchongfu_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testchongfu_temp]
GO
CREATE TABLE [dbo].[testchongfu_temp] (
    [ID] [int]  NOT NULL ,
    [Title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [getdate] [datetime] NULL
) ON [PRIMARY]
GO

这里测试Title重复的数据

--新建一个临时表的索引，并“忽略重复的值”,xxx作为唯一表识值字段
CREATE UNIQUE INDEX [temp] ON [dbo].[testchongfu_temp]([Title]) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

--把原表的数据导入临时表，XXX为原表名
insert into testchongfu_temp Select * from testchongfu

--清空原表，并将临时表中的数据导回原表，最后删除临时表
delete testchongfu

insert into testchongfu select * from testchongfu_temp
drop table testchongfu_temp

原参考地址:http://www.cnblogs.com/idotnet8/articles/1330768.html

转载于:https://www.cnblogs.com/sendling/archive/2009/01/13/1374625.html