Возвращение не вставленной колонки в SQL Output: решение

Быстрый ответ

Если в SQL вам требуется извлечь данные из столбцов, не участвующих в операции вставки, вы можете воспользоваться подзапросом в разделе OUTPUT. Сначала с помощью оператора выводится идентификатор добавленной записи, а затем он связывается с данными из исходной таблицы. Вот так это выглядит на примере таблицы INSERTED:

INSERT INTO TargetTable (ColumnA, ColumnB)
OUTPUT INSERTED.ColumnA, INSERTED.ColumnB, 
       (SELECT AdditionalColumn FROM SourceTable WHERE ID = INSERTED.ID)
SELECT ColumnA, ColumnB FROM SourceTable;

Примечание: Замените ID на имя столбца, юникально идентифицирующего строки в SourceTable.

Расширенные способы захвата данных

Подзапросы это замечательно, но SQL предлагает и более сложные инструменты для извлечения данных из дополнительных столбцов.

Использование MERGE для сложных операций с OUTPUT

Благодаря команде MERGE вы можете выполнять более сложные операции захвата данных. Она позволяет извлекать данные как из вставляемых, так и из исходных строк без необходимости обновления. Ниже представлен пример использования этого подхода:

MERGE TargetTable AS TARGET
USING SourceTable AS SOURCE
ON 1=0 -- Создаем условие для соблюдения сценария вставки в любой случае
WHEN NOT MATCHED THEN
    INSERT (ColumnA, ColumnB)
    VALUES (SOURCE.ColumnA, SOURCE.ColumnB)
OUTPUT INSERTED.ColumnA, INSERTED.ColumnB, SOURCE.AdditionalColumn;

Подробнее об этом расскажет наш спикер на видео

Учет производительности и очистка тестовой среды

При формировании запросов, особенно работающих с большими объемами данных, важно думать о потреблении ресурсов. И после завершения экспериментов в тестовой среде не забудьте убрать за собой – удалить тестовые таблицы и данные.

Визуализация

Для более наглядного понимания работы SQL OUTPUT, когда требуется получить данные из столбца, который первоначально не вставлялся,воспользуйтесь следующей аналогией:

Представим ужин, где каждое блюдо ассоциируется со столбцом SQL-таблицы 🍽️:

| Место за столом | Блюдо (Вставляемый Столбец)  | Доступен ли доп. хлеб (Столбец, который не вставлялся) |
| --------------  | --------------------------  | ----------------------------------------------------- |
| Основное блюдо  | ✅ (Подано)                  | ❓ (Можем ли мы получить хлеб, если его нет в меню?)   |

-- Начинаем подачу основного блюда:
INSERT INTO DinnerTable (MainCourse)
OUTPUT inserted.MainCourse, -- ✅ Основное блюдо на столе
       ??? -- ❓ Хлеб заказан, но его пока нет...
VALUES ('Лазанья');

И вот что в итоге получается:

Всё довольно просто: **Получаем то, что подано на тарелку** 🍽️ 🥘

- OUTPUT позволяет увидеть только то, что **непосредственно перед нами** (вставленные столбцы).
- Если хотим хлеб (невставленные столбцы), нужно **инициировать дополнительный запрос**! 🍞➡️🔄

-- Правильный способ получить хлеб:
SELECT * FROM SideDish BREAD WHERE ...; -- Вызываем хлеб отдельно!

Использование продвинутых приёмов SQL

Объединение MERGE и OUTPUT

Важно понимать механизм работы команды MERGE, особенно когда нужно получить дополнительные данные из исходной таблицы. Укажите предикат ON так, чтобы условие 1=0 всегда выполнялось, что позволяет рассматривать каждую строку как новую запись.

Изучение CTE и ROW_NUMBER()

При подготовке данных для вставки используйте подход, основанный на Общих Табличных Выражениях (CTE). Их комбинация с функцией ROW_NUMBER() и оператором ORDER BY позволит создать упорядоченный набор данных, который пригодится при вставке в таблицы с сложной структурой.

Всё об свойстве IDENTITY

Свойство IDENTITY позволяет автоматически присваивать уникальные значения новым строкам. Научитесь пользоваться этим свойством эффективно, это поможет вам усовершенствовать стратегии захвата данных.

Полезные материалы

1. Microsoft Docs: Клауза OUTPUT (Transact-SQL) – Официальная документация — Информация о глубоких знаниях Microsoft относительно использования OUTPUT клаузы.
2. Использование Output для получения ID после вставки в SQL Server – Обсуждение на Stack Overflow — Делятся опытом работы с OUTPUT клаузой для извлечения идентификаторов значения после вставки данных.
3. SQL Server: Использование клаузы OUTPUT для отслеживания изменений, сделанных с помощью MERGE – Database Journal — Практическое применение изменений SQL MERGE, отслеживаемых через OUTPUT-клаузу.
4. Как использовать выходные параметры в хранимой процедуре SQL – C# Corner — Руководство по использованию выходных параметров в хранимых процедурах.
5. Отладка SQL Server CLR: функции, триггеры, хранимые процедуры — Советы по отладке функций, триггеров и хранимых процедур SQL Server CLR.