SQL Server Calculate the total update

Question

I have this code, I know this is not the most efficient way to get this job done but what can I say! I am new to SQL and I do everything line by line. Basically I am updating specific fields in a table. I need to find the total update, as you see the list is too long and I have more to it so I know need to know how many total updates I have. Does anybody know if I can do that in SQL other than just copying and pasting the code into word document and count the number of the word Update. Can temp table do something like that?

UPDATE tblMEP_MonthlyData   
    SET Consumption = 51634

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND Consumption != 51634
AND tblMEP_Sites.Name LIKE '%Altgeld%'
AND Type = 1
AND BillingMonth = '2012-11-01 00:00:00.000'

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

UPDATE tblMEP_MonthlyData   
    SET Consumption = 38370

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Altgeld%'
AND Consumption != 38370
AND Type = 1
AND BillingMonth = '2012-10-01 00:00:00.000'

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

UPDATE tblMEP_MonthlyData   
    SET Consumption = 108610


FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Avond%'
AND Consumption != 108610

AND Type = 1
AND BillingMonth = '2012-8-01 00:00:00.000'

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

UPDATE tblMEP_MonthlyData   
    SET Consumption = 107923

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Avond%'
AND Consumption != 107923

AND Type = 1
AND BillingMonth = '2012-9-01 00:00:00.000'


---------------------------------------------------------------------------------
UPDATE tblMEP_MonthlyData   
    SET Consumption = 1442

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Belmont-cragin Pre-k%'
AND Consumption != 1442

AND Type = 1
AND BillingMonth = '2012-7-01 00:00:00.000'

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


UPDATE tblMEP_MonthlyData   
    SET Consumption = 1477


FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Belmont%'
AND Consumption != 1477


AND Type = 1
AND BillingMonth = '2012-8-01 00:00:00.000'

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


UPDATE tblMEP_MonthlyData   
    SET Consumption = 1636

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Belmont%'
AND Consumption != 1636

AND Type = 1
AND BillingMonth = '2012-9-01 00:00:00.000'

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


UPDATE tblMEP_MonthlyData   
    SET Consumption = 1451


FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Belmont%'
AND Consumption != 1451

AND Type = 1
AND BillingMonth = '2012-10-01 00:00:00.000'

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


UPDATE tblMEP_MonthlyData   
    SET Consumption = 1615

FROM  tblMEP_Sites

JOIN tblMEP_Meters
ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

JOIN tblMEP_Monthlydata
ON tblMEP_MonthlyData.MeterID = tblMEP_Meters.ID

WHERE ProjectID = 40
AND tblMEP_Sites.Name LIKE '%Belmont%'
AND Consumption != 1615

AND Type = 1
AND BillingMonth = '2012-11-01 00:00:00.000'

Answer 1

You may collect number of rows affected by each statement using @@ROWCOUNT variable. If you would like to store this anywhere, you may add the following lines after each block of code:

set @RowsAffected=@@ROWCOUNT
insert into MyTableWithTOtals (RowsAffected) values (@RowsAffected)

Of course you should declare @RowsAffected variable in the beginning. Using this way you will be able to count number of rows and number of update statements as well.

Answer 2

Please use

select @@Rowcount

at the end of the update statements.

Answer 3

What you want is @@ROWCOUNT.

After each update this is set to the number of rows that have been updated. (See here.)

In terms of putting this into your code, you might do something like:

declare @TotalRowsint = 0;

update . . .

set @TotalRows= @TotalRows+ @@ROWCOUNT;

update . . .

set @TotalRows= @TotalRows+ @@ROWCOUNT;

Then you can select the total. If you want the count for each update, you could use print @@ROWCOUNT instead. Or, insert the value into the table.

When using @@ROWCOUNT, be careful, because most SQL statements affect it. So, a safe thing to do is to assign it to another variable immediately and then use that variable:

declare @TotalRows int = 0, @NumRows int;

update . . .

set @NumRows = @@ROWCOUNT;
set @TotalRows= @TotalRows + @NumRows;
print @NumRows;
insert into AllMyUpdates(which, numrows)
    select 'This update', @NumRows;

update . . .