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

最新推荐文章于 2023-10-14 06:40:31 发布

wangzhpwang

最新推荐文章于 2023-10-14 06:40:31 发布

阅读量2.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： T-SQL查询

本文链接：https://blog.youkuaiyun.com/wangzhpwang/article/details/37725039

T-SQL查询专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一种使用SQL进行表操作的方法，包括创建源表和临时表、插入数据、设置主键及唯一索引等步骤，并探讨了通过截断源表、插入临时表数据的方式进行高效数据更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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