Merge statement Insert into multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge statement Insert into multiple tables

Author

Topic

singhswat
Starting Member

4 Posts

Posted - 2014-07-23 : 21:34:49

Hello,

Can we insert into multiple table using merge statement ? If not then what are the alternatives ... please suggest.

I'm using SQL Server 2008 R2 and below is my MERGE query... can somebody kindly let me know where am I going wrong.

Problem:

-> I'm checking if the record exist in Contact table or not. If it exist then I will insert into employee table else I will insert into contact table then employee table.



	
	WITH Cont as 
		( Select ContactID from Contact where ContactID=@ContactID)
	MERGE Employee as NewEmp
	Using Cont as con 
			ON NewEmp.ContactID=NewEmp.EmployeeID
		When NOT Matched
		THEN
		--1st insert into Contact table 
		INSERT ([ContactID]	,[NameStyle] ,[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailAddress]
				,[EmailPromotion],[ModifiedDate])
		VALUES (@ContactID,@NameStyle,@Title,@FirstName,@MiddleName ,@LastName,@Suffix,@EmailAddress,@EmailPromotion
				,@ModifiedDate)
		--2nd insert into Employee table
				INSERT ([EmployeeID],[NationalIDNumber],[ContactID],[LoginID] ,[ManagerID],[JobTitle],[BirthDate]
			 ,[MaritalStatus],[Gender],[HireDate],[SalariedFlag],[VacationHours],[SickLeaveHours],[CurrentFlag]	 
			 ,[ModifiedDate] )
			VALUES (@EmployeeID,@NationalIDNumber,@ContactID,@LoginID,@ManagerID,@JobTitle,@BirthDate,@MaritalStatus
			 ,@Gender,@HireDate,@SalariedFlag,@VacationHours,@SickLeaveHours,@CurrentFlag,@ModifiedDate)
		When Matched
		THEN --Insert in Employee table only
 			INSERT ([EmployeeID],[NationalIDNumber],[ContactID],[LoginID] ,[ManagerID],[JobTitle],[BirthDate]
			 ,[MaritalStatus],[Gender],[HireDate],[SalariedFlag],[VacationHours],[SickLeaveHours],[CurrentFlag]	 
			 ,[ModifiedDate] )
			VALUES (@EmployeeID,@NationalIDNumber,@ContactID,@LoginID,@ManagerID,@JobTitle,@BirthDate,@MaritalStatus
			 ,@Gender,@HireDate,@SalariedFlag,@VacationHours,@SickLeaveHours,@CurrentFlag,@ModifiedDate)
;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-24 : 08:23:39

Short answer: NO. Merge works on source and target tables (one of each). However, you can use the OUTPUT clause of UPDATE and INSERT like this:


declare @foo table (a int, b int)
declare @bar table (a int, b int)
declare @inserted table (action nvarchar(10), a int, b int)

insert into @foo values
	(1,2),
	(3,4)

insert into @bar values
	(1,22),
	(2,44)

merge @foo foo
using @bar bar
on foo.a = bar.a
when matched then
   update set foo.b = bar.b
   --output inserted.* into @updated
when not matched then
   insert (a,b) values
	(bar.a, bar.b)
output $Action, inserted.* into @inserted
;

select * from @foo
select * from @inserted

produces:

and


action	a	b
INSERT	2	44
UPDATE	1	22

This may give you want you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources