Multiple table insertion using single statement is this possible?

This was one of question asked in a interview. The obvious answer would be no without using a view until SQL 2008. But in SQL 2008 this is possible without the use of view by means of a newly introduced feature called Composible DML. This purpose of this blog is to explain how we can use this feature to achieve this requirement.

A typical scenario where you need this type of multiple inserts is case where you need to populate parent child tables simultaneously using values input from user.Here you can apply the composable DML feature to get this done using a single statement to do two simultaneous inserts.Lets see how this can be done 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,

Value2 varchar(100),

Value3 varchar(100)

)

Now lets look at insert procedure which does simultaneous insert to Parent Child tables using Composable DML feature.

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

As you see from above, this makes use of composable DML to do simultaneous insertion to parent and child tables.