問(wèn)題描述

我想知道是否有一種方法可以在表中不包含該記錄的情況下將記錄插入到表中?

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?

是否有可以執(zhí)行此操作的查詢(xún)，或者我是否需要一個(gè)存儲(chǔ)過(guò)程?

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

推薦答案

你不說(shuō)是什么版本的 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，這是我最初認(rèn)為問(wèn)題所問(wèn)的問(wèn)題，但它在沒(méi)有 WHEN MATCHED 子句的情況下是有效的，只有一個(gè) 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);

就執(zhí)行成本而言，當(dāng)要完成插入時(shí)，兩者看起來(lái)大致相等...

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

首次運(yùn)行計(jì)劃圖像的鏈接

但是在第二次運(yùn)行時(shí)沒(méi)有插入操作時(shí)，馬修的答案看起來(lái)成本更低.我不確定是否有辦法改善這一點(diǎn).

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.

鏈接以計(jì)劃第二次運(yùn)行的圖像

測(cè)試腳本

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

這篇關(guān)于僅當(dāng)表中不存在記錄時(shí)才插入記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！