Массовое добавление записей и получение ID в MS SQL

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

Чтобы вставить несколько записей одновременно и получить их идентификаторы в SQL Server, используйте оператор OUTPUT:

INSERT INTO MyTable (Col1, Col2)
OUTPUT INSERTED.ID
VALUES ('Val1', 'Val2'), ('Val3', 'Val4');

В PostgreSQL к запросу добавьте RETURNING:

INSERT INTO MyTable (Col1, Col2)
VALUES ('Val1', 'Val2'), ('Val3', 'Val4')
RETURNING ID;

В MySQL запросите последний вставленный идентификатор и количество изменённых строк после вставки данных, либо используйте транзакцию для получения каждого ID.

Вставка записей и сохранение идентификаторов

Чтобы сохранить идентификаторы для последующего использования, обратитесь к переменным таблиц:

DECLARE @Identities TABLE (ID INT);

-- Сохраняем полученные идентификаторы
INSERT INTO MyTable (Col1, Col2)
OUTPUT INSERTED.ID INTO @Identities
VALUES ('Val1', 'Val2'), ('Val3', 'Val4');

-- Идентификаторы теперь доступны в @Identities

Таким способом идентификаторы будут доступны для дальнейшего использования в скрипте.

Вставка большого объема данных без использования курсоров

Отказ от использования курсоров дает возможность ускорить массовую вставку. Воспользуйтесь оператором OUTPUT для операций, не требующих их применения:

-- Не используем курсоры!
INSERT INTO TableA (Column)
OUTPUT INSERTED.ID, INSERTED.Column -- Здесь хранятся идентификаторы и значения столбцов
SELECT Column FROM TableB; -- Перенос данных из TableB в TableA

Данная команда позволяет быстро переместить данные из TableB в TableA, записывая ID без необходимости работать с курсорами.

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

Представьте железнодорожную станцию, где у каждого поезда (записи) есть свой номер вагона (идентификатор).

Применяем SQL для получения этих идентификаторов:

-- Приготовьтесь к отправлению!
INSERT INTO Stations(TrainName) 
OUTPUT inserted.TrainNumber 
VALUES ('Express'), ('Local'), ('Freight');

Ж/д станция 🚉
Поезда: 🚄🚃🚂
Табло: 📢
"Номер Експресса: 1️⃣"
"Номер Пригородного: 2️⃣"
"Номер Грузового: 3️⃣"

Так станция объявляет номер каждого отправляющегося поезда. Очень удобно и наглядно!

Синхронизация после вставки

После вставки данных в TableA возможно потребуется синхронизировать новые ID со старыми ID из TableB:

-- Разберем на примере сопоставления ID
DECLARE @MapTable TABLE (OldID INT, NewID INT);

-- Делимся новыми данными!
INSERT INTO TableA (Column)
OUTPUT TableB.ID, INSERTED.ID INTO @MapTable(OldID, NewID)
SELECT Column FROM TableB;

-- Обновляем TableB с новыми ID
UPDATE TableB
SET TableB.NewId = MT.NewID
FROM @MapTable MT
WHERE TableB.ID = MT.OldID;

Данный запрос поясняет как обновить TableB, используя новые ID из TableA, обходя курсоры. Эффективность сопоставления идентификаторов на высоком уровне!

Возможные проблемы

Использование оператора OUTPUT имеет свои нюансы:

• Триггеры: Возможно влияние на работу оператора OUTPUT.
• Привилегии: Убедитесь, что у вас есть все необходимые разрешения для использования OUTPUT.
• Транзакции: Помните, что откаты транзакций влияют на финальные значения ID.

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

1. Статья о применении оператора OUTPUT при вставке данных – SQLServerCentral
2. Дискуссия о способах получения идентификаторов в SQL Server Management Studio 2012 – Stack Overflow
3. Официальная документация Microsoft по использованию оператора OUTPUT в SQL Server – Microsoft Learn
4. Обсуждение методов получения идентификатора последней вставленной строки в SQL Server – Stack Overflow
5. Пояснение значения и использования функции SCOPE_IDENTITY() в SQL Server – SQL Authority