INSERT IF NOT EXISTS 但以任何一种方式返回身份 [英] INSERT IF NOT EXISTS but return the identity either way

问题描述

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

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.

仅当该值不存在时，我如何有效地插入该值，但无论该值已经存在还是仅使用 SQL(可能是事务)新插入，才能获取该值的 ID.

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

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

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)

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

I can get the inserted ID from an insertion:

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

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

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.

推荐答案

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

You can use an IF statement to do this

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

这篇关于INSERT IF NOT EXISTS 但以任何一种方式返回身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！