ВСТАВЬТЕ, ЕСЛИ НЕ СУЩЕСТВУЕТ, но верните личность в любом случае

У меня есть 3 таблицы: audioFormats, videoFormats и fileInfo. У меня транзакция такая, что когда я вставляю в таблицу fileInfo, эта вставка включает FK из аудиоформатов и видеоформатов. Вставка в последние таблицы происходит, если аудиоформат или видеоформат уже не находятся в этих таблицах, тогда сгенерированное (или существующее) значение ID вставляется в fileInfo.

Как эффективно вставлять значение только в том случае, если это значение не существует, но получить идентификатор значения, существует ли оно уже или было недавно вставлено, используя только SQL (и, возможно, транзакцию).

Я могу вставить значение, если оно еще не существует:

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

Я могу получить вставленный идентификатор из вставки:

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

SCOPE_IDENTITY не даст мне идентификатор, если вставка не состоялась. Я могу выполнить скалярный запрос, чтобы получить идентификатор после возможной вставки, но похоже, что я мог бы сделать все это с максимально одним SELECT и INSERT.

Ответ 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

Ответ 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 быстрее, чем тот, который действительно возвращает строки.

Mine, вероятно, будет быстрее в тех случаях, когда значение уже существует большую часть времени, потому что тогда будет сделан ровно один запрос (и не будет запроса IF NOT EXISTS). Я подозреваю, что нулевая проверка тривиальна.

Ответ 3

Я думаю, вы можете использовать MERGE для этого.

Это дает вам беспроблемное решение. Некоторая яркая искра может улучшить это, используя предложение output, чтобы получить целевой идентификатор, но ниже будет работать нормально.

Я добавил ссылку на тестер запросов 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 для примера

Ответ 4

Если таблица audioFormats имеет автоинкремент IDENTITY(1,1) PK, вы можете получить только что вставленный идентификатор простым выбором:

SELECT MAX(ID) FROM audioFormats

EDIT:

Как упоминалось в комментарии, этот подход применим, когда только один запрос вставляется в таблицу.

В противном случае вы можете посмотреть IDENT_CURRENT ('table_name').

IDENT_CURRENT Возвращает последнее значение идентичности, сгенерированное для указанную таблицу или представление. Последнее генерируемое значение идентичности может быть любой сеанс и любую область.

Ответ 5

Вам нужно будет гарантировать, что столбец audioformat уникален с помощью ограничения UNIQUE и помещает ваш код в try/catch.

Попробуйте вставить. Если он терпит неудачу, то поймать, запросить новую запись и вернуть существующий идентификатор.

Сначала вы можете попробовать выполнить запрос. Но если кто-то вставит между началом вашей партии и, когда вы вставляете, БД все равно выбросит исключение.