Inserting Data from a 'WITH' statement

[bmacbmac]

Hello. I have the following query:

with A as (
select T.pr,T.sa,T.block,T.tlot, r=T.tlot-rank()over(partition by T.pr,T.sa,T.block order by T.tlot)
from (Select pr,CONVERT(INT,lot) tlot,block,sa from lotblock WHERE isnumeric(lot + 'e0') = 1) T
)
select pr,sa,block,MIN(tlot),MAX(tlot)
from A
group by pr,sa,block order by pr,sa,block

The query runs fine, but I am trying to insert the results into another table:

insert into #temp
with A as (
select T.pr,T.sa,T.block,T.tlot, r=T.tlot-rank()over(partition by T.pr,T.sa,T.block order by T.tlot)
from (Select pr,CONVERT(INT,lot) tlot,block,sa from lotblock WHERE isnumeric(lot + 'e0') = 1) T
)
select pr,sa,block,MIN(tlot),MAX(tlot)
from A
group by pr,sa,block order by pr,sa,block

I keep getting the error: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'A'.


How do I need to modify this to insert it into a table?

Thanks!

Brian

 

[drlex]

Brian,
It's merely an issue of ordering your statements; the Update or Insert needs to follow the common table expresssion.

;with A as (SELECT ....)

INSERT into #temp
SELECT * from A

soi là, soi carré

 

[bmacbmac]

Perfect thanks!