Merge keyword ,Insert,update,delete supports sql server 2014

Posted by Raja_89 under Sql Server on 12/29/2016 | Points: 10 | Views : 349 | Status : [Member] | Replies : 2
Hai
Can we use merge keyword a feature (which was introduced in sql server 2008) in sql server 2014

if so how can we implement it in stored procedure


Regards




Responses

Posted by: Allemahesh on: 1/2/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
Try the below link. It have full example on how to use Merge keyword in SP.

https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/3/2017 [Member] [MVP] Platinum | Points: 25

Up
0
Down
CREATE PROCEDURE uspSampleMergeSP

@pID int,
@pName varchar(100),
@pMarks int
AS
BEGIN TRY

MERGE Table1 TGT
USING (SELECT @pID as ID, @pName as Name, @pMarks Marks) SRC
ON TGT.ID = SRC.ID
WHEN MATCHED THEN UPDATE SET
TGT.Name = SRC.Name
,TGT.Marks = SRC.Marks
WHEN NOT MATCHED THEN INSERT
(
ID
,Name
,Marks
)
VALUES
(
SRC.ID
,SRC.Name
,SRC.Marks
);

RETURN 0

END TRY
BEGIN CATCH
RETURN -1
END CATCH
GO


if you want to pass more than one record as input, then do as follows:
1. create user defined table type as udtTable1 (ID, Name, Marks)
2. create stored procedure with input param as table-type
CREATE PROCEDURE uspSampleMergeSP @pTblStudent udtTable1

reference:
https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response