在SQL 2012中将选择数据插入临时表

Question

问题：如何在sql 2012中将复杂的选择数据插入临时表

select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join  [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type

我尝试过遵循解决方案

select * into #temptable from
(select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join  [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type)

我跟踪了错误

Incorrect syntax near ')'.

当我通常运行select语句时，我得到预期的数据

Answer 1

就语法而言，以下代码是正确的：

SELECT *
INTO #Temptable
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY Ppt.Type) AS Item_Code,
           Ppt.Type AS Type,
           '11/02/19 09:51' AS Created_Dt
    FROM Product AS Psi
      INNER JOIN Db1..Items AS Ios ON Ios.Icode = Psi.Icode
      INNER JOIN Db2..Types AS Ppt ON Ppt.Type = Ios.Type );

通常，您可以捕获CTE中的逻辑并将CTE插入到临时表中。

USE SomeDB;
WITH CTE AS 
(

    SELECT *
    FROM
    (
       SELECT ROW_NUMBER() OVER(ORDER BY Ppt.Type) AS Item_Code,
            Ppt.Type AS Type,
            '11/02/19 09:51' AS Created_Dt
       FROM Product AS Psi
        INNER JOIN Db1..Items AS Ios ON Ios.Icode = Psi.Icode
        INNER JOIN Db2..Types AS Ppt ON Ppt.Type = Ios.Type )
)

INSERT INTO #T
SELECT * FROM CTE

Answer 2

问题是，您正在将数据从未命名的数据源插入到表中。

select * into #temptable from
(select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join  [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type) as tbl

就这样吧，你的问题就会解决了。我刚刚为要插入数据的源添加了一个别名。