时间戳

首页 > 解决方案 > 关键字“into”附近的语法不正确，尝试创建视图时出现问题？

sql-server - 关键字“into”附近的语法不正确，尝试创建视图时出现问题？

问题描述

这个问题是当我尝试在一个 .dbo 中创建视图时。在我删除到语句之后，它在关键字 FROM 附近向我显示了另一个与以前相同的错误。

WITH temptable(strict,DataItem, description) AS
(
    SELECT
        strict,
        CAST(LEFT(description, CHARINDEX(CHAR(10), description + CHAR(10)) - 1) AS nvarchar(50)),
        CAST(STUFF(description, 1, CHARINDEX(CHAR(10), description + CHAR(10)), '') AS nvarchar(50))
    from safezone where id= 12
    UNION all

    SELECT
        strict,
        CAST(LEFT(description, CHARINDEX(CHAR(10), description + CHAR(10)) - 1) AS nvarchar(50)),
        CAST(STUFF(description, 1, CHARINDEX(CHAR(10), description + CHAR(10)), '') AS nvarchar(50))
    FROM temptable
    WHERE
        description > ''
        
)


SELECT
    strict,
    DataItem
into info FROM temptable
ORDER BY strict
option (maxrecursion 0)

SELECT replace(info.DataItem,'  ',' ')as predmet, info.strict,po.yearwriteoff,po.position
from info 
left join safezone on info.strict=safezone.strict
order by strict

我怎样才能解决这个问题

标签： sql-server

解决方案

您不能在视图中插入记录，但看起来您只想临时存储最后一个查询的第一个结果集。您可以使用多个公用表表达式 (CTE)，每个 CTE 都可以使用所有以前的 CTE。

尝试这个：

-- first CTE definition
WITH temptable(strict,DataItem, description) AS
(
    SELECT
        strict,
        CAST(LEFT(description, CHARINDEX(CHAR(10), description + CHAR(10)) - 1) AS nvarchar(50)),
        CAST(STUFF(description, 1, CHARINDEX(CHAR(10), description + CHAR(10)), '') AS nvarchar(50))
    from safezone where id= 12
    UNION all

    SELECT
        strict,
        CAST(LEFT(description, CHARINDEX(CHAR(10), description + CHAR(10)) - 1) AS nvarchar(50)),
        CAST(STUFF(description, 1, CHARINDEX(CHAR(10), description + CHAR(10)), '') AS nvarchar(50))
    FROM temptable
    WHERE
        description > ''
        
),
-- second CTE definition
info(strict, DataItem) as
(
    SELECT
        strict,
        DataItem
    FROM temptable
)
-- final selection
SELECT replace(info.DataItem,'  ',' ')as predmet, info.strict,po.yearwriteoff,po.position
from info 
left join safezone on info.strict=safezone.strict
order by strict
option (maxrecursion 0);

备注：第二个 CTE 在这里没有附加值。仅用于演示目的。您可以只使用temptable(with alias info?) 而不是info在最后一个选择中。

推荐阅读