StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Remove the fields that you're already matching on from the update, there's no need to do that as they'd already be the same.</p> <p>You can add something like this before:</p> <pre><code>SET NOCOUNT ON ; DECLARE @RowCounts TABLE ( MergeAction NVARCHAR(10) ) ; DECLARE @InsertCount INT , @UpdateCount INT; </code></pre> <p>And then this after immediately after the merge:</p> <pre><code> OUTPUT $ACTION INTO @RowCounts ; SELECT @InsertCount = [INSERT] , @UpdateCount = [UPDATE] FROM ( SELECT MergeAction , 1 AS ROWS FROM @RowCounts ) P PIVOT ( COUNT(ROWS) FOR MergeAction IN ( [INSERT], [UPDATE] ) ) AS PVT ; </code></pre> <p>That'll give you your row counts.</p> <p>Edit: </p> <p>Link for more info on OUTPUT clause <a href="http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx" rel="nofollow">http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx</a></p> <p>The whole shebang:</p> <pre><code>SET NOCOUNT ON ; DECLARE @RowCounts TABLE ( MergeAction NVARCHAR(10) ) ; DECLARE @InsertCount INT , @UpdateCount INT; Merge into User_Grps as ug using [ExcImport-S2] as i on ( ug.uNSID = i.uNSID and ug.prime_ID = i.prime_ID and i.uNSID is not null ) WHEN MATCHED THEN UPDATE SET ug.gNSID = i.gNSID , ug.gAlias = isnull(i.gAlias,ug.gAlias) , ug.last_ud = GETDATE ( ) WHEN NOT MATCHED THEN -- UPDATE SET @i += 1; INSERT (uNSID , gNSID , gAlias , Prime_ID , Last_UD ) VALUES (i.uNSID , i.gNSID , i.gAlias , i.Prime_ID , GETDATE ( ) ) OUTPUT $ACTION INTO @RowCounts ; SELECT @InsertCount = [INSERT] , @UpdateCount = [UPDATE] FROM ( SELECT MergeAction , 1 AS ROWS FROM @RowCounts ) P PIVOT ( COUNT(ROWS) FOR MergeAction IN ( [INSERT], [UPDATE] ) ) AS PVT ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload