仅当表中不存在记录时才插入记录

本文介绍了仅当表中不存在记录时才插入记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想知道是否有一种方法可以在表中不包含该记录的情况下将记录插入到表中?

I'm wondering if there is a way to insert a record into a table only if the table does not already contain that record?

是否有可以执行此操作的查询，或者我是否需要一个存储过程?

Is there a query that will do this, or will I need a stored procedure?

推荐答案

你不说是什么版本的 SQL Server.如果 SQL Server 2008 你可以使用 MERGE

You don't say what version of SQL Server. If SQL Server 2008 you can use MERGE

注意:通常将 Merge 用于 Upsert，这是我最初认为问题所问的问题，但它在没有 WHEN MATCHED 子句的情况下是有效的，只有一个 WHEN NOT MATCHED 子句也适用于这种情况.示例用法.

NB: It is usual to use Merge for an Upsert which is what I originally thought the question was asking but it is valid without the WHEN MATCHED clause and just with a WHEN NOT MATCHED clause so does work for this case also. Example Usage.

CREATE TABLE #A( [id] [int] NOT NULL PRIMARY KEY CLUSTERED, [C] [varchar](200) NOT NULL) MERGE #A AS target USING (SELECT 3, 'C') AS source (id, C) ON (target.id = source.id) /*Uncomment for Upsert Semantics WHEN MATCHED THEN UPDATE SET C = source.C */ WHEN NOT MATCHED THEN INSERT (id, C) VALUES (source.id, source.C);

就执行成本而言，当要完成插入时，两者看起来大致相等...

In terms of execution costs the two look roughly equal when an Insert is to be done...

首次运行计划图像的链接

但是在第二次运行时没有插入操作时，马修的答案看起来成本更低.我不确定是否有办法改善这一点.

but on the second run when there is no insert to be done Matthew's answer looks lower cost. I'm not sure if there is a way of improving this.

链接以计划第二次运行的图像

测试脚本

select * into #testtable from master.dbo.spt_values CREATE UNIQUE CLUSTERED INDEX [ix] ON #testtable([type] ASC,[number] ASC,[name] ASC) declare @name nvarchar(35)= 'zzz' declare @number int = 50 declare @type nchar(3) = 'A' declare @low int declare @high int declare @status int = 0; MERGE #testtable AS target USING (SELECT @name, @number, @type, @low, @high, @status) AS source (name, number, [type], low, high, [status]) ON (target.[type] = source.[type] AND target.[number] = source.[number] and target.[name] = source.[name] ) WHEN NOT MATCHED THEN INSERT (name, number, [type], low, high, [status]) VALUES (source.name, source.number, source.[type], source.low, source.high, source.[status]); set @name = 'yyy' IF NOT EXISTS (SELECT * FROM #testtable WHERE [type] = @type AND [number] = @number and name = @name) BEGIN INSERT INTO #testtable (name, number, [type], low, high, [status]) VALUES (@name, @number, @type, @low, @high, @status); END