Azure Synapse - получение значения идентификатора вставленной строки

Question

У нас есть задание ETL, в котором нужно вставить строку в Table X, а затем получить IDENTITY значение для строки, вставленной в Table X для использования в последующих преобразованиях.IDENTITYиспользуется как суррогатный ключ, например, как здесь.

В SQL Server можно использовать SCOPE_IDENTITY, но это недоступно в Azure Synapse (ранее - хранилище данных SQL Azure).

Простой пример

╔═══════════════╦═══════╗
║ Id (IDENTITY) ║ Value ║
╠═══════════════╬═══════╣
║             1 ║ abc   ║
║             2 ║ def   ║
╚═══════════════╩═══════╝

Задание вставит значение "hij".

╔═══════════════╦═══════╗
║ Id (IDENTITY) ║ Value ║
╠═══════════════╬═══════╣
║             1 ║ abc   ║
║             2 ║ def   ║
║             3 ║ hij   ║
╚═══════════════╩═══════╝

Задание должно знать значение Id = 3.

Примечание: выполнение MAX(Id) не сработает, поскольку столбец IDENTITY не гарантирует инкрементных значений, а другие вставки могут в это время попасть в таблицу.

Есть ли у кого-нибудь предлагаемое решение для этого?

Answer 1

По состоянию на октябрь 2022 г. SCOPE_IDENTITY по-прежнему не поддерживается в Azure Synapse.

Метод, предложенный в комментариях Джоэлом Кокраном, кажется самым безопасным и законным способом:

1. создать GUID() в приложении, которое создает задание
2. вставлятьyour_guidи другие метаданные вакансий в вашу таблицу вакансий
3. SELECT id FROM job_table WHERE guid = your_guid

Работает без ошибок уже более 2-х лет.

Answer 2

Я обнаружил ту же «проблему» при присоединении к проекту на моей текущей работе, в настоящее время мы используем первый запрос, упорядоченный по (desc) вставленному столбцу даты и времени:

      SELECT TOP 1 identity_col
FROM schema.table
ORDER BY datetime_col DESC

Я знаю, что это подвержено ошибкам, я хотел использовать SCOPE_IDENTITY(), но это настолько хорошо, насколько это возможно... может быть.