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;

Advertisements
  1. #1 by Henri on April 6, 2009 - 11:34 pm

    Thx 🙂 Soo logical, but I missed it as well 🙂

  2. #2 by Juan Ignacio on May 18, 2009 - 4:59 am

    Thanks 😉

  3. #3 by neda monajati on February 27, 2011 - 10:24 pm

    Hi
    Tanks a lot 😉

  4. #4 by cybervoodoo on June 28, 2011 - 8:06 pm

    Нихера не работает почему-то…..(((

  5. #5 by Narendar T on September 6, 2011 - 4:26 pm

    Thanks

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

%d bloggers like this: