Insert This, Insert That: Inserting with CTEs

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:

Example #1

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!

Example #2

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.

Example #3

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. 🙂

Advertisements

6 thoughts on “Insert This, Insert That: Inserting with CTEs

  1. Thank you very much for showing where to insert the records into a table in your CTE! That is something that I’ve been searching for during the past week. Your example made it easy to understand.

  2. Hey, so I was looking at this and:

    Firstly, thank you for writing about one of the “hidden” features of CTEs. By hidden I mean few people mention it. It’s technically on MSDN but only in the examples, the actual explanation part only really mentions that CTE’s have to be followed by INSERT, SELECT, UPDATE, etc. and that tended to get taken as meaning inserting into a table by using the CTE values.

    Secondly, a situation in which INSERT behaviour is actually pretty useful is MERGEs, you can do a MERGE and use your CTE to cut down on the records the MERGE tries to match. Then your MERGE can be inserting into the CTE itself. It’s actually quite elegant.

    Thirdly, any info on how SQL handles you INSERTing into a CTE that has a JOIN in its SELECT? I’ll probably just try this out to be honest but figured it didn’t hurt to ask.

    1. Thanks and you are welcome! 🙂 Yes, you can insert using a CTE that has a JOIN in its SELECT. If you have any problems or issues with it, please let me know. I’ll do my best to help.

      Thanks for the info about the merge and such. I haven’t tried using a CTE with a MERGE yet but it sounds interesting. I may have to try it out. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s