How to Use Variables, IF and CASE in Database Interactions with TransactSQL - Generating serial numbers (or sequences) and cumulative sums using the UPDATE statement

(Page 2 of 5 )

Let us consider that I have a table with several rows. I would like to add a new column of the numeric type and fill it with serial numbers. You can achieve this by  using an UPDATE statement together with an auto-updatable variable.

Before proceeding with the script, we need to create a table with existing rows and add a new column to fill in the serial numbers. Execute the following command, which creates a new table named "SampleCustomers" (with exactly the same structure and rows as is available in the Customers table) along with a new column SlNo (filled with default zeroes):

select 0 SlNo, * into SampleCustomers from customers

At this point, if you open the SampleCustomers table, it would contain a new column named SlNo (filled with zeroes) together with all the columns and rows from existing Customers table.

Now, I would like to update all the rows in that table with serial numbers using an UPDATE statement. The following is the script that will achieve the same: 

use northwind
go
set nocount on
go
declare @SlNo int
set @SlNo = 0
update SampleCustomers
   set @SlNo = SlNo = @SlNo + 1

print 'Updated Successfully'
go

You can also achieve this using the IDENTITY column. But I would like to demonstrate the efficiency of variables together with the UPDATE statement.

We can create not only sequences, but also running or cumulative sums and update them as part of the table itself. Let us now create another sample table which contains an Order wise price list and an extra column to store running sums (defaults with zeroes) as follows:

select OrderID, Amt, 0 RunningSum into SampleOrders
from
(
select orderid, sum(unitprice * quantity) amt
from [order details]
group by orderid
)as a

Now, we can update the RunningSum with values using the following script:

use northwind
go
set nocount on
go
declare @sum int
set @sum = 0
update SampleOrders
   set @sum = RunningSum = @sum + Amt

print 'Updated Successfully'
go 

Next: Introducing the IF statement in T-SQL >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: How to Use Variables, IF and CASE in Database Interactions with TransactSQL

· Hello guys,This is second in series focusing on the programming with...    · I was able to retrieve value of a column which is not updated!Declare @Location...    · Wow that is great. I'm going to try a couple of tricks and see what happens. ...   >>> Post your comment now!