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

Question

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

如何仅在该值不存在时有效地插入该值，同时获取该值的 ID（无论该值已存在还是仅使用 SQL（可能是事务）新插入）。

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

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

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

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

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

Answer 1

您可以使用 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

，也可以像这样执行：

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

Answer 2

我将一个答案标记为正确，因为它与我最终的 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 的答案可能会更快，因为 IF NOT EXISTS 查询比实际执行的查询更快返回行。

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

Answer 3

我认为你可以使用 MERGE 来实现这一点。

这为您提供了无交易的解决方案。一些聪明的火花可能能够使用 output 子句来获取目标 id 来改进这一点，但下面的方法就可以正常工作。

我在下面添加了 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

在此处运行此查询：查询示例的 StackOverflow 链接

Answer 4

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

SELECT MAX(ID) FROM audioFormats 

编辑：

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

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

IDENT_CURRENT 返回为某个对象生成的最后一个标识值
指定的表或视图。最后生成的身份值可以是
任何会话和任何范围。

Answer 5

您需要通过 audioformat 列是唯一的>UNIQUE 约束，并将代码放入 尝试/捕获。

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

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