sql server unique index 开启 ignore_dup_key 去除重复数据

1源表

SET NOCOUNT ON
 GO
IF OBJECT_ID('A01_Infoset_Modify_R') IS NOT NULL
    DROP TABLE A01_Infoset_Modify_R
GO
CREATE TABLE A01_Infoset_Modify_R
(
	A00                 UNIQUEIDENTIFIER,
	InputTemplateID     UNIQUEIDENTIFIER,
	NodeID              UNIQUEIDENTIFIER,
	RID                 UNIQUEIDENTIFIER NOT NULL,
	RightValue          INT DEFAULT 0
)
--添加主键
ALTER TABLE A01_Infoset_Modify_R ADD CONSTRAINT PK_RID PRIMARY KEY CLUSTERED(RID)

INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT 'D2CD0E36-40C4-A306-E306-DD8E2518AD18',
       '14E82E1B-FF1B-4B7E-8291-2194802A28FA',
       '95CD2F58-8BDF-4E92-BB3B-62C1D29B8D0C',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '83CC9F21-4C5B-EBF6-1C3D-23848DBB0638',
       'F3E48A7D-D182-4574-9DE8-85F20C4C347B',
       '5D3EC30D-E997-4DAD-B32E-13D5756B2380',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '7AEDC9A7-482F-BFFE-009C-0C9A61B8765E',
       'BD2E0298-311B-4A12-B91B-39FEE3814AC2',
       'A336BF06-8E24-4400-9CDC-1C97EF3738B7',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT 'D2CD0E36-40C4-A306-E306-DD8E2518AD18',
       '14E82E1B-FF1B-4B7E-8291-2194802A28FA',
       '95CD2F58-8BDF-4E92-BB3B-62C1D29B8D0C',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '83CC9F21-4C5B-EBF6-1C3D-23848DBB0638',
       'F3E48A7D-D182-4574-9DE8-85F20C4C347B',
       '5D3EC30D-E997-4DAD-B32E-13D5756B2380',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '7AEDC9A7-482F-BFFE-009C-0C9A61B8765E',
       'BD2E0298-311B-4A12-B91B-39FEE3814AC2',
       'A336BF06-8E24-4400-9CDC-1C97EF3738B7',
       NEWID(),
       '2'
GO

INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT 'D2CD0E36-40C4-A306-E306-DD8E2518AD18',
       '14E82E1B-FF1B-4B7E-8291-2194802A28FA',
       '95CD2F58-8BDF-4E92-BB3B-62C1D29B8D0C',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '83CC9F21-4C5B-EBF6-1C3D-23848DBB0638',
       'F3E48A7D-D182-4574-9DE8-85F20C4C347B',
       '5D3EC30D-E997-4DAD-B32E-13D5756B2380',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '7AEDC9A7-482F-BFFE-009C-0C9A61B8765E',
       'BD2E0298-311B-4A12-B91B-39FEE3814AC2',
       'A336BF06-8E24-4400-9CDC-1C97EF3738B7',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT 'D2CD0E36-40C4-A306-E306-DD8E2518AD18',
       '14E82E1B-FF1B-4B7E-8291-2194802A28FA',
       '95CD2F58-8BDF-4E92-BB3B-62C1D29B8D0C',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '83CC9F21-4C5B-EBF6-1C3D-23848DBB0638',
       'F3E48A7D-D182-4574-9DE8-85F20C4C347B',
       '5D3EC30D-E997-4DAD-B32E-13D5756B2380',
       NEWID(),
       '2'
GO
INSERT INTO A01_Infoset_Modify_R
  (
    A00,
    InputTemplateID,
    NodeID,
    RID,
    RightValue
  )
SELECT '7AEDC9A7-482F-BFFE-009C-0C9A61B8765E',
       'BD2E0298-311B-4A12-B91B-39FEE3814AC2',
       'A336BF06-8E24-4400-9CDC-1C97EF3738B7',
       NEWID(),
       '2'
GO 

2临时表

if exists (select * from tempdb..sysobjects where id = object_id(N'tempdb..#A01_Infoset_Modify_R2') and type='U')
    DROP TABLE #A01_Infoset_Modify_R2
GO
CREATE TABLE #A01_Infoset_Modify_R2
(
	A00                 UNIQUEIDENTIFIER,
	InputTemplateID     UNIQUEIDENTIFIER,
	NodeID              UNIQUEIDENTIFIER,
	RID                 UNIQUEIDENTIFIER NOT NULL,
	RightValue          INT DEFAULT 0
)
ALTER TABLE #A01_Infoset_Modify_R2 ADD CONSTRAINT PK_#A01_Infoset_Modify_R2_RID 
PRIMARY KEY CLUSTERED(RID)
GO
--创建唯一索引并 ignore_dup_key=on
CREATE UNIQUE INDEX UIX_#A01_Infoset_Modify_R2_A00_NODE_INPUTTEMPLATEID 
ON #A01_Infoset_Modify_R2(NODEID, A00, INPUTTEMPLATEID)
WITH (IGNORE_DUP_KEY = ON)
GO
INSERT INTO #A01_Infoset_Modify_R2

SELECT *
FROM   A01_Infoset_Modify_R
GO

SELECT *
FROM   #A01_Infoset_Modify_R2

3思考

1.                Truncate 源表
2. go
3. insert  into 源表 select * from 临时表
4. drop 临时表

如果是分区表，可以通过分区切换后，再drop 临时表来实现