sql-server - 如何在各种条件下从 CTE 插入多个变量？

Question

有一张表，我使用with子句进行 SQL 查询。

正如标题中所述，我想在不同的条件下插入或插入@tempTableA。@tempTableBSomeTable

但它不起作用。我对 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

标签： sql-serversql-insertcommon-table-expression

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