如果不存在则插入，但以任何方式返回身份

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

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

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

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

相关文章