In what is quickly (or not so quickly depending on who you ask) turning into a mini-series on CTEs, not too long ago I wrote about performing updates using CTEs in my short novel “Yes! You Can Update with CTEs!” This week I’m writing about inserting records using a CTE. If you’re not quite sure what a CTE even is, you can read about the basics in my post “CTEs Demystified“. Now on to the fun stuff.
It’s actually quite simple and pretty much the same as doing an update except it’s an insert. As with pretty much most CTEs, the main reason you would use one would be to help simplify your code by replacing sub queries/derived tables. This week I’m looking for my top sales people of all time. To keep this simple, I’m just going to create a simple table to simply store a few items. Got it? Good! If you want to follow along, I’m using the AdventureWorks database for SQL Server 2008. You can download it here.
First, comes the table. I’m going to use a temporary table for this example. You can use a real table, if you want to.
create table #TopSalesPeepsOfAllTime ( SalesPersonID int not null ,TerritoryName nvarchar(50) ,TotalSalesQuota money );
In this example, the top sales people are the ones who have made over $10,000,000 in total sales. The query for it can be done without using a CTE such as in the code below:
insert into #TopSalesPeepsOfAllTime select SalesPerson.SalesPersonID ,SalesTerritory.Name ,TopSalesPeeps.totalSalesQuota from Sales.SalesPerson inner join Sales.SalesTerritory on SalesPerson.TerritoryID = SalesTerritory.TerritoryID inner join (select SalesPersonID ,sum(SalesQuota) totalSalesQuota from Sales.SalesPersonQuotaHistory group by SalesPersonID having sum(SalesQuota) >= 10000000.00) TopSalesPeeps on Sales.SalesPerson.SalesPersonID = TopSalesPeeps.SalesPersonID; select SalesPersonID ,TerritoryName ,TotalSalesQuota from #TopSalesPeepsOfAllTime; drop table #TopSalesPeepsOfAllTime;
After running the code above, the results could look something like this:
Using SQL Server 2005 and beyond, you can accomplish the same task using a CTE. Personally, I like CTEs because it makes future maintenance easier. That’s just my opinion.
;with TopSalesPeeps (SalesPersonID,totalSalesQuota) as ( select SalesPersonID ,sum(SalesQuota) totalSalesQuota from Sales.SalesPersonQuotaHistory group by SalesPersonID having sum(SalesQuota) >= 10000000.00 ) insert into #TopSalesPeepsOfAllTime select SalesPerson.SalesPersonID ,SalesTerritory.Name ,TopSalesPeeps.totalSalesQuota from TopSalesPeeps inner join Sales.SalesPerson on TopSalesPeeps.SalesPersonID = SalesPerson.SalesPersonID inner join Sales.SalesTerritory on SalesPerson.TerritoryID = SalesTerritory.TerritoryID; select SalesPersonID ,TerritoryName ,totalSalesQuota from #TopSalesPeepsOfAllTime;
Running the code above, we should see the same results as the first query. Don’t take my word for it. Feel free to try it yourself on your test/development system!
If you read my post on updating CTEs, you may or may not recall that I was able to do an update on the CTE name itself which updated the underlying table. It worked on that one so I wondered if it’d work using an insert. Guess what? It does! However, I’m not sure how practical it or useful that would be. Personally, I feel it’s more straightforward to join to a CTE when doing your updates, inserts, and deletes. However, there could possibly be a time when you need to do an insert on the CTE itself even though I can’t think of one now. So if you wanted to do that, here’s one way you could.
;with TopSalesPeeps (salesPersonID, TerritoryName, totalSalesQuota) as ( select SalesPersonID ,TerritoryName ,TotalSalesQuota from #TopSalesPeepsOfAllTime ) insert into TopSalesPeeps select SalesPersonQuotaHistory.SalesPersonID ,SalesTerritory.Name ,sum(SalesPersonQuotaHistory.SalesQuota) from Sales.SalesPersonQuotaHistory inner join Sales.SalesPerson on SalesPersonQuotaHistory.SalesPersonID = SalesPerson.SalesPersonID inner join Sales.SalesTerritory on SalesPerson.TerritoryID = SalesTerritory.TerritoryID group by SalesPersonQuotaHistory.SalesPersonID ,SalesTerritory.Name having sum(SalesPersonQuotaHistory.SalesQuota) >= 10000000.00 select SalesPersonID ,TerritoryName ,TotalSalesQuota from #TopSalesPeepsOfAllTime;
If we run this code, we should get the same results as the other two.
And there you have it! Inserts using CTEs. For my next CTE post, I’ll discuss deleting records using CTEs. It shouldn’t be much different other than you’re removing records instead of inserting or deleting. 🙂