Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MERGE Statement MULTIPLE INSERT into different tables Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2014 7:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:51 PM
Points: 8, Visits: 26
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)
;

Post #1595775
Posted Wednesday, July 23, 2014 10:32 PM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 809, Visits: 5,139
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);


Post #1595791
Posted Thursday, July 24, 2014 1:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 752, Visits: 1,323
1st you cannot do it in Merge statement. However, to get a fair bit of idea what Merge can do check this

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

if you have any further issue let us know.
Post #1595797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse