SQL Server合并语句 [英] SQL Server Merge statement
本文介绍了SQL Server合并语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在存储过程中执行merge语句.我需要在更新和插入期间计算行数.如果我使用一个公共变量来获取更新的行(用于更新和插入),但我有何不同,这是我从更新中获得的计数,这是我从插入中获得的计数.请给我一个更好的方法
I am doing merge statement in my stored procedure. I need to count the rows during updates and inserts. If i use a common variable to get the updated rows (for both update and insert) how i can differ, this is the count which i got from update and this is the count which i got from insert. Please give me a better way
推荐答案
您可以创建一个表变量来保存操作类型,然后在其中保存OUTPUT
伪$action
列.
You can create a table variable to hold the action type then OUTPUT
the pseudo $action
column to it.
示例
/*Table to use as Merge Target*/
DECLARE @A TABLE (
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[C] [varchar](200) NOT NULL)
/*Insert some initial data to be updated*/
INSERT INTO @A
SELECT 1, 'A' UNION ALL SELECT 2, 'B'
/*Table to hold actions*/
DECLARE @Actions TABLE(act CHAR(6))
/*Do the Merge*/
MERGE @A AS target
USING (VALUES (1, '@a'),( 2, '@b'),(3, 'C'),(4, 'D'),(5, 'E')) AS source (id, C)
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET C = source.C
WHEN NOT MATCHED THEN
INSERT (id, C)
VALUES (source.id, source.C)
OUTPUT $action INTO @Actions;
/*Check the result*/
SELECT act, COUNT(*) AS Cnt
FROM @Actions
GROUP BY act
返回
act Cnt
------ -----------
INSERT 3
UPDATE 2
这篇关于SQL Server合并语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文