MERGE Statement MULTIPLE INSERT into different tables

Question

MERGE Statement MULTIPLE INSERT into different tables

singhswat

SSC Veteran

Points: 208
More actions
July 23, 2014 at 7:38 pm

#397109

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)
;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

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.

Re-reading your question, I don't think MERGE is what you want. As I understand it, your logic should be really simple:

IF NOT EXISTS (SELECT 1 FROM Contact WHERE SomeField = @SomeValue)

BEGIN

INSERT INTO Contact(fieldlist) VALUES (valuelist);

END

INSERT INTO Employee(fieldlist) VALUES (valuelist);

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

1st you cannot do it in Merge statement. However, to get a fair bit of idea what Merge can do check this[/url]

your logic is really simple. try to work with solution shared by pietlinden.

if you have any further issue let us know.