BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.