Calculating rows processed and updated

  • Hi Professionals

    I am running a procedure to calculate the total number of rows updated but I also wan the number of rows it hasnt updated, could I be going wrong somewhere would I need to calculated a select count(*) from the table minus the total rows updated or something.

    anyway here is my procedure

    USE [TestData]

    GO

    /****** Object: StoredProcedure [dbo].[cleanseSMPN] Script Date: 08/09/2013 09:25:30 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER procedure [dbo].[cleanseSMPN]

    as

    begin

    declare @TotalRows int =0, @numrows int;

    --Update the Software Manufacturer and the Product name

    begin

    truncate table myupdates

    UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer,

    productname = dbref.Amended_Product_Name

    FROM dbo.newtable dbsource

    INNER JOIN (

    SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,

    Raw_Product_Name,Amended_Product_Name

    FROM datalookuptable

    GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,

    Raw_Product_Name,Amended_Product_Name

    ) dbref

    ON dbref.Raw_SW_Manufacturer = softwaremanufacturer

    --and dbref.Raw_Product_Version = dbsource.productversion

    and dbref.Raw_Product_Name = productname

    --------------------------------------------------------------------------------------------------------

    set @NumRows = @@ROWCOUNT;

    set @TotalRows= @TotalRows + 1;

    insert into myupdates(NumRows,TotalRows)

    values (@NumRows,@TotalRows)

    ---------------------------------------------------------------------------------------------------------

    end

    END

  • Right now @TotalRows will always be 1 at the end. Is that the idea?

    If you want the rows not updated, you'll need to do a rowcount (there are many ways to do this) and substract @numrows from it.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply