Updating table with table variable
You can use them to calculate pretty much any aggregate you can think of.
How about a grand total of how much we’ve added to the amount column?
We update a table column by increasing the amount column by 10% for a selection of rows, and we assign a value to a variable, by incrementing @counter by 1 for every row.
After the UPDATE, we can view the value of the @counter variable to see how many rows we actually modified.
Note: Don’t divide by @count if you haven’t actually updated any rows, or you’ll get a division by zero error. Some calculations, like running totals, require that the data is ordered properly to function correctly.
The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#): The syntax for creating table variables is quite similar to creating either regular or temporary tables.Just like the SELECT statement, the UPDATE statement executes row-by-row on the table being updated.This means that the SET clause of the UPDATE statement is evaluated once for every row in the updated table.Unlike temporary or regular table objects, table variables have certain clear limitations.
The rule checks for temporary tables which are created, used and deleted in the same batch, and do not have explicit indexes created.In this example, we want to know the highest batch number of the rows we’ve updated.For each row, we use the CASE expression to check if the row’s batch number is higher than @max Batch Number.This kind of temporary tables can be replaced with table variables.