way to ignore intermediate column in an INSERT SELECT statement?

Question

For a insert ... select statement, is there a way to ignore a variable that is required for the select statement but should be inserted?

I'm using a rank function to select which data to insert. Unfortunately the rank function can't be called in the where clause.

insert into target_table(v1
                        ,v2
                        ,v3
                        )
select v1
      ,v2
      ,v3
      ,RANK() over (partition by group_col
                    order by order_col desc
                   ) as my_rank
from source_table
where my_rank > 1

The result is the following error:

Msg 121, Level 15, State 1, Line 1 The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

I know I can do this using a temporary table but would like to keep it in a single statement if possible.

Answer 1

Wrap your main query inside a subquery.

INSERT INTO target_table
    (v1, v2, v3)
    SELECT q.v1, q.v2, q.v3
        FROM (SELECT v1, v2, v3, 
                     RANK() OVER (PARTITION BY group_col ORDER BY order_col DESC) AS my_rank
                  FROM source_table) q
        WHERE q.my_rank > 1;

For SQL Server 2005+, you could also use a CTE:

WITH cteRank AS (
    SELECT v1, v2, v3, 
           RANK() OVER (PARTITION BY group_col ORDER BY order_col DESC) AS my_rank
        FROM source_table
)
INSERT INTO target_table
    (v1, v2, v3)
    SELECT v1, v2, v3
        FROM cteRank
        WHERE my_rank > 1;