Thursday, August 25, 2011

Composable DML continued...

This is a follow up post of


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.