Composable DML continued...

This is a follow up post of

http://visakhm.blogspot.com/2011/08/multiple-table-insertion-using-single.html

In the above post I've illustrated how Composable DML feature can be applied for simultaneous insert to parent child tables. I was trying to use this feature in a similar scenario when I discovered something. The scenario can be depicted as below

CREATE TABLE Parent

(

ID int IDENTITY(1,1) NOT NULL,

Value1 varchar(100)

)

CREATE TABLE Child

( ID int IDENTITY(1,1) NOT NULL,

Parent_ID int REFERENCES Parent(ID),

Value2 varchar(100),

Value3 varchar(100)

)

Now see what happens when you apply the Composable DML feature here

CREATE PROC InsertParentChild

@Value1 varchar(100),

@Value2 varchar(100),

@Value3 varchar(100)

AS

INSERT INTO Child

(Parent_ID,Value2,Value3)

SELECT

FROM ID,

@Value2,

@Value3

(

INSERT INTO Parent (Value1)

OUTPUT INSERTED.ID

VALUES(@Value1)

)t

GO

Compiling the above proc gives you this error

Msg 356, Level 16, State 1, Line 5

The target table 'dbo.Child' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship.

when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK_...'

Thus you can find that Composable DML cant be applied on table if it forms part of foreign key constraint.