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.