Интересное применение оператора merge и функции output

Сегодняшнее сообщение посвящается такой задаче. Есть таблица, имеющая identity-поле. В эту таблицу выполняется вставка некоторого количества строк. Требуется получить генерируемые в ходе этой операции identity-поля. А, возможно, и что-нибудь еще!

Когда то, давно, когда я еще не знал о существовании функции output  мне пришлось для решения этой задачи делать вставку с помощью построчной обработки строк по курсору. Конечно, можно было бы исхитриться и вычислить перед вставкой с помощью инструкции dbcc checkident текущее значение автоинкрементного поля, а также значение приращения с помощью функции ident_seed. После этого, по окончании вставки, можно перебрать список identity-полей, начиная с того, которое было получено при запуске dbcc checkident, с шагом, равным ident_seed, в количестве, равном значению системной переменной @@rowcount, вычисленной сразу после окончания вставки. Такой способ неудобен тем, что в многопользовательской среде из-за дополнительных шагов (вычисления dbcc checkident, ident_seed) таблицу необходимо блокировать. Причем если другие соединения читают ее без хинта nolock (для задачи описанной выше), то можно воспользоваться сериализуемым уровнем изоляции транзакции. Если же они читают таблицу в режиме грязного чтения, то и сериализуемого уровня изоляции окажется мало.

С появлением функции output все изменилось. С помощью этого замечательного изобретения задачу поиска генерируемых identity-полей можно решить с помощью одного sql-выражения. Например, как в сценарии ниже:

if object_id ( N'tempdb..#OldId', N'U' ) is not null

      drop table #OldId

if object_id ( N'tempdb..#NewId', N'U' ) is not null

      drop table #NewId

if object_id ( N'tempdb..#NewIdLog', N'U' ) is not null

      drop table #NewIdLog

create table #OldId

(

      Id                int               not null,

      CurrentDate       datetime          not null,

      vcDescr           nvarchar ( 100 )  not null,

      primary key clustered ( Id asc )

) on [PRIMARY]

create table #NewId

(

      Id          int identity ( 1, 1 )   not null,

      vcDescr     nvarchar ( 100 )        not null,

      primary key clustered ( Id asc )

) on [PRIMARY]

create table #NewIdLog

(

      Id                int         not null,

      CurrentDate       datetime    not null default ( getdate () ),

      primary key clustered ( Id asc )

) on [PRIMARY]

insert into #OldId ( Id, CurrentDate, vcDescr )

      values

            ( 1, getdate (), N'' ),

            ( 2, getdate (), N'' ),

            ( 3, getdate (), N'' )

insert into #NewId ( vcDescr )

     

      output inserted.Id

      into #NewIdLog ( Id )

     

      select vcDescr

      from #OldId

Предположим, однако, что требуется логировать не только информацию о новых значениях автоинкрементного поля, но и о каких-либо соответствующих им столбцах таблицы-источника, которые не вставляются в таблицу приемник. Например, если в задаче выше требуется не только заполнять поле Id таблицы #NewIdLog, но и обновлять поле CurrentDate, делая его равным, значению одноименного поля таблицы #OldId и соответствующим вставляемому полю vcDescr. Если в последнем запросе в параметрах функции output к списку столбцов добавить столбец CurrentDate, то это вызовет синтаксическую ошибку. Как раз тут и поможет оператор merge. Запрос можно переписать так:

merge #NewId as trg

using

(

      select vcDescr, CurrentDate

      from #OldId

) as src on 1 = 0

when not matched then insert ( vcDescr )

      values ( src.vcDescr )

output inserted.Id, src.CurrentDate

into #NewIdLog ( Id, CurrentDate )

;

В приведенном решении, используется странное на первый взгляд условие соединения записей источника и приемника: " 1 = 0 ". Все дело в том, что по условию задачи нам надо вставить все записи из таблицы #OldId в таблицу #NewId, поэтому для соединения необходимо любое условие, которое будет ложно всегда во времени и в пространстве, например, то, что один равно нулю.