只在表中未存在记录时插入记录

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?

是否存在这样的查询，或者是否需要存储过程?

4 个解决方案

#1

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

您不会说SQL Server的版本。如果SQL Server 2008可以使用MERGE。

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.

NB:对Upsert使用Merge是很常见的，这是我最初认为的问题，但是没有WHEN matches子句和a WHEN NOT MATCHED子句也是有效的，所以在这种情况下也适用。使用例子。

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...

就执行成本而言，插入操作时，两者看起来大致相等……

Link to plan images for first run

链接到计划图像的第一次运行。

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.

但在第二次尝试中，当没有插入的时候，马修的回答看起来成本更低。我不确定是否有改进的方法。

Link to plan images for second run

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

Test Script

测试脚本

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

#2

IF NOT EXISTS 
    (SELECT {Columns} 
    FROM {Table} 
    WHERE {Column1 = SomeValue AND Column2 = SomeOtherVale AND ...}) 
INSERT INTO {Table} {Values}

#3

In short, you need a table guaranteed to provide you the ability to return one row:

简而言之，您需要一个保证能够返回一行的表:

Insert dbo.Table (Col1, Col2, Col3....
Select 'Value1', 'Value2', 'Value3',....
From Information_Schema.Tables
Where Table_Schema = 'dbo'
    And Table_Name = 'Table'
    And Not Exists  (
                    Select 1
                    From dbo.Table
                    Where Col1 = 'Foo'
                        And Col2 = 'Bar'
                        And ....
                    )

I've seen this variation in the wild as well:

我在野外也见过这种变异:

Insert Table (Col1, Col2, Col3....
Select 'Value1', 'Value2', 'Value3'....
From    (
        Select 1 As Num
        ) As Z
Where Not Exists    (
                    Select 1
                    From Table
                    Where Col1 = Foo
                        And Col2 = Bar
                        And ....
                    )

#4

I have to vote for adding a CONSTRAINT. It's the simplest and the most robust answer. I mean, looking at how complicated the other answers are I'd say they're much harder to get right (and keep right).

我必须投票赞成增加约束条件。这是最简单、最可靠的答案。我的意思是，看看其他的答案有多复杂，我想说他们很难做对(并且保持正确)。

The downsides: [1] it's not obvious from reading the code that uniqueness is enforced in the DB [2] the client code has to know to catch an exception. In other words, the guy coming after you might wonder "how did this ever work?"

缺点:[1]从阅读代码中不难看出，在DB[2]中强制执行惟一性，客户端代码必须知道捕获异常。换句话说，跟在你后面的那个人可能会问，“这是怎么回事?”

That aside: I used to worry that throwing/catching the exception was a performance hit but I did some testing (on SQL Server 2005) and it wasn't significant.

除此之外:我曾经担心抛出/捕获异常会影响性能，但我做了一些测试(在SQL Server 2005上)，结果并不显著。

#1