SQL INSERT IF NOT EXISTS 但以任何一种方式返回身份

INSERT IF NOT EXISTS but return the identity either way

提问by Charles Burns

I have 3 tables: audioFormats, videoFormats, and fileInfo. I have a transaction such that when I insert into the fileInfo table, that insert includes an FK from audioFormats and videoFormats. An insertion into the latter tables takes place if the audio format or video format are not already in those tables, then the generated (or existing) ID value is inserted into fileInfo.

我有 3 个表：audioFormats、videoFormats 和 fileInfo。我有一个事务，当我插入到 fileInfo 表中时，该插入包含来自 audioFormats 和 videoFormats 的 FK。如果音频格式或视频格式不在这些表中，则会插入到后面的表中，然后将生成的（或现有的）ID 值插入到 fileInfo 中。

How do I efficiently insert a value only if that value does not exist, but get the ID of the value whether it already exists or was freshly inserted using only SQL (and perhaps a transaction).

仅当该值不存在时，我如何有效地插入一个值，但获取该值的 ID，无论它是否已经存在或仅使用 SQL（可能是一个事务）刚刚插入。

I can insert a value if it does not already exist:

如果它不存在，我可以插入一个值：

INSERT INTO audioformats (audioformat)
VALUES(@format)
WHERE NOT EXISTS (SELECT 1 FROM audioformats WHERE audioformat = @format)

I can get the inserted ID from an insertion:

我可以从插入中获取插入的 ID：

INSERT INTO audioFormats (audioFormat)
VALUES ('Test')
SET @audioFormatId = SCOPE_IDENTITY()

SCOPE_IDENTITY won't give me an ID value if no insertion took place. I can execute a scalar query to get the identity after a possible insertion, but it seems like I should be able to do all of this with at most one SELECT and INSERT.

如果没有插入，SCOPE_IDENTITY 不会给我一个 ID 值。我可以执行标量查询以在可能的插入后获取身份，但似乎我最多应该能够使用一个 SELECT 和 INSERT 来完成所有这些。

采纳答案by Hogan

You can use an IF statement to do this

您可以使用 IF 语句来执行此操作

IF NOT EXISTS(SELECT TOP 1 1 FROM audioformats WHERE audioformat = @format)
BEGIN
  INSERT INTO audioFormats (audioFormat)
  VALUES ('Test')
  SET @audioFormatId = SCOPE_IDENTITY()
END
ELSE
BEGIN
  SELECT @audioFormatID = ID FROM audioformats WHERE audioformat = @format
END

or you could do it like this:

或者你可以这样做：

INSERT INTO audioformats (audioformat)
  SELECT @format
  FROM audioFormats
  WHERE NOT EXISTS (SELECT TOP 1 1 FROM audioformats WHERE audioformat = @format)

SELECT @audioFormatID = ID FROM audioformats WHERE audioformat = @format

回答by Charles Burns

I marked an answer as correct because it was the most similar to my final SQL, but it did not really fulfill the original requirement of using at most one query and one insert. The following does, and is what I ended up using:

我将一个答案标记为正确，因为它与我的最终 SQL 最相似，但它并没有真正满足最多使用一个查询和一个插入的原始要求。以下是，并且是我最终使用的：

-- One select
SELECT @audioFormatId = id
FROM audioformats
WHERE audioformat = @audioFormat;

-- Optionally one insert
IF @audioFormatId IS NULL AND @audioFormat IS NOT NULL
BEGIN
    INSERT INTO audioFormats (audioFormat)
    VALUES (@audioFormat)
    SET @audioFormatId = SCOPE_IDENTITY()
END

Hogan's answer will probablybe faster when most calls to the query do in fact perform an insertion because an IF NOT EXISTS query is faster than one that actually does return rows.

当对查询的大多数调用实际上执行插入时，Hogan 的回答可能会更快，因为 IF NOT EXISTS 查询比实际返回行的查询快。

Mine will probablybe faster in cases where the value already exists most of the time because then exactly one query (and no IF NOT EXISTS query) will be made. I suspect the null check is trivial.

在大部分时间该值已经存在的情况下，我的可能会更快，因为这样只会进行一个查询（并且没有 IF NOT EXISTS 查询）。我怀疑空检查是微不足道的。

回答by Neil Moss

I think you can use MERGEfor this.

我想你可以用MERGE这个。

This gives you a transactionless solution. Some bright spark might be able to improve this using the outputclause to get the target id, but below will work just fine.

这为您提供了无事务解决方案。一些明亮的火花可能能够使用output子句来改进这一点来获取目标 id，但下面的工作会很好。

I've added a link to the StackOverflow query tester below.

我在下面添加了一个指向 StackOverflow 查询测试器的链接。

-- Merge example
-- Get ID of existing row or insert new row

-- Initialise unit test data
declare @AudioFormatId int;
declare @AudioFormat nvarchar(50)
declare @tblAudioFormats TABLE (AudioFormatId int identity, AudioFormat nvarchar(50)   );
insert into @tblAudioFormats(AudioFormat) values ('MP3'), ('WAV');

-- set query criteria
set @AudioFormat = 'MP3' -- query below returns 1 - updating MP3
--set @AudioFormat = 'WAV' -- query below returns 2 - updating WAV
--set @AudioFormat = 'MIDI' -- query below returns 3 - inserting MIDI

-- Insert or update AudioFormat and return id of target audio format.
merge
  @tblAudioFormats as Target
using
  (select @AudioFormat as AudioFormat) as source(AudioFormat)
on 
  (source.AudioFormat = target.AudioFormat)
when matched then
  update set @AudioFormatID = target.AudioFormatId
when not matched then
  insert(AudioFormat) values (source.AudioFormat);

if @AudioFormatId is null set @AudioFormatId = scope_identity()

-- return ID of target audio format
select @AudioFormatId as TargetAudioFormatId

Run this query here: Query StackOverflow link for sample

在此处运行此查询：Query StackOverflow link for sample

回答by Merlyn Morgan-Graham

You'll need to guarantee your audioformatcolumn is unique via a UNIQUEconstraint, and put your code in a try/catch.

您需要audioformat通过UNIQUE约束保证您的列是唯一的，并将您的代码放在try/catch 中。

Try to insert. If it fails, then catch, query for the new entry, and return the existing ID.

尝试插入。如果失败，则捕获，查询新条目，并返回现有 ID。

You could try querying first. But if someone inserts between the start of your batch and when you insert, the DB will throw an exception anyhow.

您可以先尝试查询。但是如果有人在您的批处理开始和您插入之间插入，无论如何数据库都会抛出异常。

回答by sll

If audioFormats table has autoincrement IDENTITY(1,1)PK you can get just inserted ID by simple select:

如果 audioFormats 表具有自动增量IDENTITY(1,1)PK，您可以通过简单的选择获取插入的 ID：

SELECT MAX(ID) FROM audioFormats 

EDIT:

编辑：

As was mentioned in comment this approach is applicable when only one query inserting into a table.

正如评论中提到的，当只有一个查询插入到表中时，这种方法适用。

Otherwise you can take a look at the IDENT_CURRENT('table_name')function.

否则，您可以查看IDENT_CURRENT('table_name')函数。

IDENT_CURRENT Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_CURRENT 返回为指定表或视图生成的最后一个标识值。生成的最后一个标识值可以用于任何会话和任何范围。