Share This!

Tuesday, February 12, 2013

Temp Tables in SQL Server

We all know you can create variables in SQL Server...
declare @customer varchar(20)
set @customer='a customer'

...but what if there is a need to store more complex data?
As it turns out, there is an easy way to accomplish that as well.

declare @csrlist Table(customer varchar(20), CSR varchar(25), counts int)

--get the counts of customer service reps orders for each customer.
insert into @csrlist (customer, csr, counts)
select distinct customer, Csr, COUNT(1) counts
from Purchase_Order
where Csr is not null
group by customer, csr

The resulting in-memory table can be inserted to, deleted from, updated, just like any real data table.
--find the CSR with the most orders for each customer
insert into @csrlist2 (customer, CSR)
    (select customer, Csr from @csrlist A where counts=(select MAX(counts) from @csrlist B where a.customer=b.customer))

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

No comments:

Post a Comment

Contact Us


Email *

Message *