Error when using insert with CTE and ranking function

Question

I have a table temp.Results which contain Employee info. This table contains info used by HR. All changes to the employee records are in the table.i.e.

select * from temp.Results where ID=1
1,'2 main st','salem','2009-01-01','2000-01-01'
1,'34 elm st','acton','2013-03-09','2000-01-01'

Datevalidated is when we entered latest info.DateProcessed is the first time we entered employee info.

 WITH ordered as( 
select ID, name, address,city, DateValidated, DateProcessed
,ROW_NUMBER() over (partition by DateValidated
       order by DateValidated desc) as rn from 
  aa.temp.Results (nolock) where id=31  
  ) 
  insert into tempResults2(ID, name, address,city, DateValidated, DateProcessed)
select ID, name, address,city, DateValidated, DateProcessed from ordered where rn = 1 ;

I tried getting the above query to get the latest info of each employee into a teable but get this error.

Invalid object name 'tempResults2'

How to resolve this?

Thanks MR

Answer 1

You can use where clause with select * into .

WITH ordered as
( 
    select 
        ID, name, address,city, DateValidated, DateProcessed,
        ROW_NUMBER() over (partition by DateValidated order by DateValidated desc) as rn 
    from aa.temp.Results  where id=31  
  ) 

select * into tempResults2 from ordered where rn = 1 ;