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

Posted by Raja_89 under Sql Server on 12/29/2016 | Points: 10 | Views : 1422 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts