SQL Server 2008新特性 Merge 详细见联机手册

系统 3791 0
      
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar ( 3 ),
@Name nvarchar ( 25 )
AS
BEGIN
SET NOCOUNT ON ;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF ( @@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES ( @UnitMeasureCode , @Name )
END
END ;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = ' ABC ' , @Name = ' Test Value ' ;
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = ' ABC ' ;
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
--
Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
(ExistingCode
nchar ( 3 ),
ExistingName
nvarchar ( 50 ),
ExistingDate
datetime ,
ActionTaken
nvarchar ( 10 ),
NewCode
nchar ( 3 ),
NewName
nvarchar ( 50 ),
NewDate
datetime
);
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar ( 3 ),
@Name nvarchar ( 25 )
AS
BEGIN
SET NOCOUNT ON ;

MERGE Production.UnitMeasure
AS target
USING (
SELECT @UnitMeasureCode , @Name ) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.
* , $action, inserted. * INTO #MyTempTable;
END ;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = ' ABC ' , @Name = ' New Test Value ' ;
EXEC InsertUnitMeasure @UnitMeasureCode = ' XYZ ' , @Name = ' Test Value ' ;
EXEC InsertUnitMeasure @UnitMeasureCode = ' ABC ' , @Name = ' Another Test Value ' ;

SELECT * FROM #MyTempTable;
-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ( ' ABC ' , ' XYZ ' );
DROP TABLE #MyTempTable;
GO

SQL Server 2008新特性 Merge 详细见联机手册


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧,狠狠点击下面给点支持吧,站长非常感激您!手机微信长按不能支付解决办法:请将微信支付二维码保存到相册,切换到微信,然后点击微信右上角扫一扫功能,选择支付二维码完成支付。

【本文对您有帮助就好】

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论