How to insert records to temptable which has ;with command in sql?

I have created a temp table which I want to fill with records.

Here is my query:

create table #temp(companysitetankid int, [SourceGradeName] varchar(50), SiteCode varchar(50));

;with cst  as (
    select companysitetankid,[SourceGradeName], SiteCode,
           row_number() over (partition by [SourceGradeName] order by [TankNumber] ) rn
    from [DataManagement_DB].[dbo].[CompanySiteTank]  where CompanyCode='Aaaa'
 )
 select * from cst where  rn >1

 insert into #temp
 select companysitetankid,[SourceGradeName], SiteCode from cst  

Here I get table cst is Invalid .

Invalid object name ‘cst’.

What am I doing wrong in the above query?

Answer

Once your CTE aliased as cst has been used in the SELECT statement, it can’t be used again. This might be what you intended to do:

WITH cst AS (
    SELECT companysitetankid, [SourceGradeName], SiteCode,
       ROW_NUMBER() OVER (PARTITION BY [SourceGradeName] ORDER BY [TankNumber]) rn
    FROM [DataManagement_DB].[dbo].[CompanySiteTank]
    WHERE CompanyCode = 'Aaaa'
)

INSERT INTO #temp
SELECT companysitetankid, [SourceGradeName], SiteCode
FROM cst
WHERE rn > 1;

If you really need to run that select to check what is being returned, then run it on the side, but remove it when you actually use it in the insert.