Как вставить в более чем одну переменную из CTE в различных условиях?

Question

Есть одна таблица, которую я использовал with предложение для запросов SQL.

Как указано в названии, я хочу вставить в @tempTableA или же @tempTableB от SomeTable в разных условиях.

Но это не работает. Что я неправильно понял в CTE? Есть ли другой способ достичь?

declare @tempTableA table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))

declare @tempTableB table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))

; with SomeTable as (
    select
        FieldId
        , FieldName1
        , FieldName2
        , FieldName3
    from tableA
    cross apply tableB)

insert @tempTableA 
select * from SomeTable where FieldName1 > 10

insert @tempTableB
select * from SomeTable where FieldName1 <= 10

Answer 1

Вы можете использовать CTE только с одним запросом после их определений (область действия CTE и последующие CTE - только для одного запроса), например:

create table Table1 (
    pkTable1 int primary key
    , fkTable2 int
    , Column1 nvarchar(max)
)

create table Table2 (
    pkTable2 int primary key
    , fkTable3 int
    , Column2 nvarchar(max)
)

create table Table3 (
    pkTable3 int primary key
    , Column3 nvarchar(max)
)

; with cte1 as (
    select pkTable1, fkTable2, Column1
    from Table1
)
, cte2 as (
    select pkTable2, fkTable3, Column2
    from Table2
)
, cte3 as (
    select * from cte1
        join cte2 on cte1.fkTable2=cte2.pkTable2
)
select * from cte3
    join Table3 on cte3.fkTable3=Table3.pkTable3

^{(*: Поскольку это просто демонстрация, не беспокойтесь о том, как ее можно оптимизировать, и запрос не имеет никакого смысла.)}

Если вы хотите использовать его в нескольких операторах, перейдите во временную таблицу или table variable или вам нужно определить CTE снова для следующего утверждения.

Например, вы можете использовать временную таблицу, например, следующую, чтобы достичь того же.

declare @tempTableA table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))
declare @tempTableB table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))
select
    FieldId
    , FieldName1
    , FieldName2
    , FieldName3
    into #t3 --Assign to temp table
from tableA
cross apply tableB

insert into @tempTableA 
select * from  #t3 where FieldName1 > 10

insert @tempTableB
select * from  #t3 where FieldName1 <= 10

drop table #t3 --Drop the table

Для вашего сценария вам не нужно CTEВы можете написать свой запрос следующим образом:

declare @tempTableA table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))
declare @tempTableB table (FieldId nvarchar(4000), FieldName1 nvarchar(4000), FieldName2 nvarchar(4000), FieldName3 nvarchar(4000))
insert into @tempTableA 
select
    FieldId
    , FieldName1
    , FieldName2
    , FieldName 
from tableA
cross apply tableB where FieldName1 > 10

insert @tempTableB
select
    FieldId
    , FieldName1
    , FieldName2
    , FieldName 
from tableA
cross apply tableB where FieldName1 <= 10