/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

MERGE and insert of master/detail rows
2010-10-31
*****************************************************/
set nocount on
go

create table dbo.MasterData
(
	MasterId int not null identity(1,1),
	Column1 varchar(32) not null,
			
	constraint PK_MasterData
	primary key clustered(MasterId)
)
go

create table dbo.DetailData
(
	DetailID int not null identity(1,1),
	MasterId int not null,
	Column2 varchar(32) not null,
			
	constraint PK_DetailData
	primary key clustered(DetailId),
	
	constraint FK_DetailData_MasterData
	foreign key(MasterID)
	references dbo.MasterData(MasterID)
)
go


declare
	@MasterData table
	(
		InternalId int not null,
		Column1 varchar(32) not null
	)
	
declare
	@DetailData table
	(
		MasterInternalId int not null,
		Column2 varchar(32) not null
	)

declare
	@Ids table
	(
		InternalId int not null,
		InsertedId int not null
	)

insert into @MasterData(InternalId, Column1)
values(0,'Master 1'),(1,'Master2')

insert into @DetailData(MasterInternalId, Column2)
values
	(0,'Detail 0.1'),(0,'Detail 0.2'),
	(1,'Detail 1.1'),(1,'Detail 1.2')

begin tran
	merge into dbo.MasterData as Target
	using (select * from @MasterData) as Source
	on Target.MasterId is null
	when not matched by target then
		insert(Column1)
		values(Source.Column1)
	output 
		inserted.MasterId, Source.InternalId
	into 
		@Ids(InsertedId, InternalId);
	
	select * from @Ids

	insert into dbo.DetailData(MasterId, Column2)
		select i.InsertedId, d.Column2
		from 
			@Ids i join @DetailData d on
				i.InternalId = d.MasterInternalId
commit
select * from dbo.MasterData
select * from dbo.DetailData
go
