2

I know I can achieve this in many ways - this is a purely academic question to see if it's possible to do this in a single statement, in order to further my knowledge of SQL.

We're splitting an old, wide table into two narrower tables, a parent and a child:

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 ) )

WorkTable contains a bunch of records from the original table - the ID from that table which we want to use as the ID in the child table (via identity_insert), and a field from that table that we actually want to set on the parent. For every row in WorkTable, therefore, we'll get 1 row in ParentTable and another in ChildTable.

Now I'm going to populate ParentTable, and also get the IDs of its newly-inserted records for the subsequent insert into 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

This works, but it's ugly. I'd much prefer if I could simplify it into one statement, whereby the inserted IDs from ParentTable are updated directly back into Worktable - thus removing the need for the @IdsMap table var that exists solely to accomplish this update.

I thought I might be able to accomplish this by using the merge as nested DML:

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

but MSSQL says no:

A nested INSERT, UPDATE, DELETE, or MERGE statement is not
allowed on either side of a JOIN or APPLY operator.

Nested DML inside a CTE similarly fails:

;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.

Is there any way to achieve what I'm looking for?

0

2 Answers 2

3

Not with a checked foreign key relationship, no. Nested DML doesn't work when the table is on either side of an enforced FK relationship. Note also that only an outer INSERT is permitted.

Temporarily not checking the FK would almost work, but the identity on the child table presents another roadblock. Inserting from nested DML is not allowed to use identity insert.

Even disabling and then reinstating the foreign key would defeat your desire to implement the operation in a single statement. Given that requirement, I think the answer to your question is no.


Example of almost working (without the child identity):

CREATE TABLE dbo.WorkTable
(
    ParentId integer NULL,
    ChildId integer NOT NULL,
    ParentField1 varchar(50) NOT NULL
);

CREATE TABLE dbo.ParentTable
(
    Id integer NOT NULL PRIMARY KEY IDENTITY,
    Field1 varchar(50) NOT NULL
);

CREATE TABLE dbo.ChildTable
(
    Id integer NOT NULL PRIMARY KEY /*IDENTITY*/,
    ParentId integer NOT NULL
        CONSTRAINT FK_CT_PT_Id
        FOREIGN KEY REFERENCES dbo.ParentTable (Id)
);

INSERT dbo.WorkTable 
    (ParentId, ChildId, ParentField1) 
VALUES 
    (1, 10, 'PF1'),
    (2, 20, 'PF2'),
    (3, 30, 'PF3');
ParentId ChildId ParentField1
1 10 PF1
2 20 PF2
3 30 PF3

Solution:

SET XACT_ABORT ON;

BEGIN TRANSACTION;

    ALTER TABLE dbo.ChildTable 
        NOCHECK CONSTRAINT FK_CT_PT_Id;

    EXECUTE (N'
        INSERT dbo.ChildTable 
            WITH (TABLOCKX)
            (Id, ParentId)
        SELECT
            P.ChildId, Id 
        FROM 
        (
            MERGE dbo.ParentTable AS P
            USING dbo.WorkTable AS WT 
                ON 0 = 1
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (Field1) 
                VALUES (WT.ParentField1)
            OUTPUT 
                WT.ChildId, 
                Inserted.Id
        ) AS P
    ');

    ALTER TABLE dbo.ChildTable 
        WITH CHECK 
        CHECK CONSTRAINT FK_CT_PT_Id;

COMMIT TRANSACTION;

execution plan

Dynamic SQL is needed to prevent an error due to the enforced constraint at the time of batch compilation.

Parent:

Id Field1
1 PF1
2 PF2
3 PF3

Child:

Id ParentId
10 1
20 2
30 3

When the child table has an identity column, the error is:

Msg 5328, Level 16, State 1
Cannot insert explicit value for the identity column 'Id' in the target table 'dbo.ChildTable' of the INSERT statement when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

-4

Why do you want it to be a one complicated query?

The task you describe can be much easily solved by a simple loop:

declare crsr cursor for
    select ParentId, ChildId, ParentField1 from WorkTable

declare @ParentId     int
declare @ChildId      int
declare @ParentField1 varchar(50)

open crsr
fetch next from crsr into @ParentId, @ChildId, @ParentField1

while @@fetch_status = 0
begin
   insert into ParentTable values(@ParentId, @ParentField1)
   insert into ChildTable values(@ChildID, @ParentId)

   fetch next from crsr into @ParentId, @ChildId, @ParentField1
end

close crsr
deallocate crsr

And of course, this way you can make as many data manipulation and conversion as needed, between reading from the source table and writing to the targets.

Additionally, you will get easy reading and modifying of the script.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.