Sei que posso conseguir isso de várias maneiras - essa é uma questão puramente acadêmica para ver se é possível fazer isso em uma única instrução, a fim de aprofundar meus conhecimentos de SQL.
Estamos dividindo uma tabela antiga e larga em duas tabelas mais estreitas, uma pai e uma filha:
create table WorkTable
( ParentId int null
, ChildId int not null
, ParentField1 varchar(50) not null )
create table ParentTable
( Id int not null primary key identity
, Field1 varchar(50) not null )
create table ChildTable
( Id int not null primary key identity
, ParentId int not null foreign key references ParentTable ( Id ) )
WorkTablecontém vários registros da tabela original - o ID dessa tabela que queremos usar como o ID na tabela filho (via identity_insert) e um campo dessa tabela que realmente queremos definir no pai. Para cada linha em WorkTable, portanto, teremos 1 linha em ParentTablee outra em ChildTable.
Agora vou preencher ParentTablee também obter os IDs de seus registros recém-inserido para a inserção subsequente em ChildTable:
declare @IdsMap table
( ParentId int not null
, ChildId int not null )
merge ParentTable dst
using (
select ChildId
, ParentField1
from WorkTable
) src on 1 = 0
when not matched by target then
insert
( Field1 )
values
( src.ParentField1 )
output inserted.Id as [ParentId]
, src.ChildId -- can't do this with a standard INSERT...OUTPUT, hence the use of MERGE
into @IdsMap
;
update wkt
set wkt.ParentId = ids.ParentId
from WorkTable wkt
join
@IdsMap ids on ids.ChildId = wkt.ChildId
Isso funciona, mas é feio. Eu preferiria muito mais se pudesse simplificá-lo em uma instrução, em que os IDs inseridos de ParentTablesão atualizados diretamente de volta Worktable- removendo assim a necessidade da @IdsMaptabela var que existe apenas para realizar essa atualização.
Achei que poderia conseguir isso usando o mergeDML aninhado:
update wkt
set wkt.ParentId = cte.ParentId
from WorkTable wkt
join
(
merge ParentTable dst
using (
select ChildId
, ParentField1
from WorkTable
) src on 1 = 0
when not matched by target then
insert
( Field1 )
values
( src.ParentField1 )
output inserted.Id as [ParentId]
, src.ChildId
) cte on cte.ChildId = wkt.ChildId
mas o MSSQL diz que não:
A nested INSERT, UPDATE, DELETE, or MERGE statement is not
allowed on either side of a JOIN or APPLY operator.
O DML aninhado dentro de um CTE também falha:
;with cte as
(
select *
from
(
merge ParentTable dst
using (
select ChildId
, ParentField1
from WorkTable
) src on 1 = 0
when not matched by target then
insert
( Field1 )
values
( src.ParentField1 )
output inserted.Id as [ParentId]
, src.ChildId
) _
)
update wkt
set wkt.ParentId = cte.ParentId
from WorkTable wkt
join
cte on cte.ChildId = wkt.ChildId
A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT
statement that is not the immediate source of rows for an INSERT statement.
Existe alguma maneira de conseguir o que estou procurando?
