問題描述

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

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

這篇關于僅當表中不存在記錄時才插入記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！