tag:blogger.com,1999:blog-2886789781940464032.post5000891160955071043..comments2023-09-30T10:36:02.745+03:00Comments on Sql Server knowledge sharing blog: Merge Statement SQL SERVER 2008Shamas DBAhttp://www.blogger.com/profile/01894071527133572968noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-2886789781940464032.post-46397712288327639532012-05-16T13:53:15.042+03:002012-05-16T13:53:15.042+03:00msdn code for all available options of Merge state...msdn code for all available options of Merge statement.<br /><br />USE AdventureWorks2012;<br />GO<br />CREATE PROCEDURE dbo.InsertUnitMeasure<br /> @UnitMeasureCode nchar(3),<br /> @Name nvarchar(25)<br />AS <br />BEGIN<br /> SET NOCOUNT ON;<br />-- Update the row if it exists. <br /> UPDATE Production.UnitMeasure<br /> SET Name = @Name<br /> WHERE UnitMeasureCode = @UnitMeasureCode<br />-- Insert the row if the UPDATE statement failed. <br /> IF (@@ROWCOUNT = 0 )<br /> BEGIN<br /> INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)<br /> VALUES (@UnitMeasureCode, @Name)<br /> END<br />END;<br />GO<br />-- Test the procedure and return the results.<br />EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';<br />SELECT UnitMeasureCode, Name FROM Production.UnitMeasure<br />WHERE UnitMeasureCode = 'ABC';<br />GO<br /><br />-- Rewrite the procedure to perform the same operations using the MERGE statement.<br />-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.<br />CREATE TABLE #MyTempTable<br /> (ExistingCode nchar(3),<br /> ExistingName nvarchar(50),<br /> ExistingDate datetime,<br /> ActionTaken nvarchar(10),<br /> NewCode nchar(3),<br /> NewName nvarchar(50),<br /> NewDate datetime<br /> );<br />GO<br />ALTER PROCEDURE dbo.InsertUnitMeasure<br /> @UnitMeasureCode nchar(3),<br /> @Name nvarchar(25)<br />AS <br />BEGIN<br /> SET NOCOUNT ON;<br /><br /> MERGE Production.UnitMeasure AS target<br /> USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)<br /> ON (target.UnitMeasureCode = source.UnitMeasureCode)<br /> WHEN MATCHED THEN <br /> UPDATE SET Name = source.Name<br /> WHEN NOT MATCHED THEN <br /> INSERT (UnitMeasureCode, Name)<br /> VALUES (source.UnitMeasureCode, source.Name)<br /> OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;<br />END;<br />GO<br />-- Test the procedure and return the results.<br />EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';<br />EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';<br />EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';<br /><br />SELECT * FROM #MyTempTable;<br />-- Cleanup <br />DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');<br />DROP TABLE #MyTempTable;<br />GOShamas DBAhttps://www.blogger.com/profile/01894071527133572968noreply@blogger.comtag:blogger.com,1999:blog-2886789781940464032.post-40676142956762073322012-03-05T15:07:41.337+03:002012-03-05T15:07:41.337+03:00This help me to create a procedure quickly.
great...This help me to create a procedure quickly.<br /><br />great postShamas DBAhttps://www.blogger.com/profile/01894071527133572968noreply@blogger.com