Archive for April, 2008

Using a Common Table Expression (CTE) with an INSERT INTO statement

I was getting a little frustrated not being able to write a SQL query that used a Common Table Expression (CTE) with my INSERT INTO statement. I could run the select fine but then when I tried to add an INSERT INTO above that it did want to work… Then I realised that INSERT INTO is part of the main query and the WITH statement needs to come before all of it… Here’s an example:

WITH ActiveUsers AS
(
   
SELECT
       
Users.UserName,
       
Users.TimezoneID,
       
ROW_NUMBER() OVER (
           
PARTITION BY
               
Users.TimezoneID
            ORDER BY
               
Users.CreatedDate ASC) as UserRowNumber
    FROM
       
Users
    WHERE
       
(Users.IsDeleted = 0))
)   
INSERT INTO [#ProcessUsers]
    (UserID, TimezoneID)
   
SELECT TOP (500) WITH TIES
       
UserName
    FROM
       
ActiveUsers
    WHERE
       
UserRowNumber <= 100
    ORDER BY
       
UserID;

5 Comments