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.