用查询将数据插入温度表中

Question

我想用查询插入临时表。

但是我有一个错误说

不正确的语法附近'(''

我使用的代码下方

select * into #Temp1
from
(
    select c.name, t.name, 0 as isSelected
    from sys.columns c
    inner join sys.types t on c.user_type_id = t.user_type_id
    where object_name(object_id) = 'tblActor'
)

Answer 1

请使用以下 -

请注意，您需要提供列的唯一名称插入。您需要像我在第二个查询中一样为链接查询提供别名。

SELECT c.name CName, t.name tName, 0 AS isSELECTED
INTO #Temp1
FROM Sys.Columns c
INNER JOIN Sys.types t ON c.user_type_id = t.user_type_id
WHERE object_name(object_id) = 'tblActor'

或

SELECT * INTO #Temp2
FROM 
(
    SELECT c.name CName ,t.name tName,0 AS isSELECTED
    FROM Sys.Columns c
    INNER JOIN Sys.types t ON c.user_type_id = t.user_type_id
    WHERE object_name(object_id) = 'tblActor'
)k

Answer 2

您在查询中有多个问题。只是做：

select c.name as c_name, t.name as t_name, 0 as isselected
into #temp1
from sys.columns c join
     sys.tables t
     on c.user_type_id = t.user_type_id
where object_name(object_id) = 'tblActdor';

我不确定该查询应该做什么。这对我来说并没有意义。而且，information_schema.columns将是更好的合理信息来源。但这将解决您遇到的错误。

Answer 3

请在最后一行的)之后使用表别名

或：

SELECT 
         col.[name] AS [ColumnName], 
         typ.[name] AS [TypeName], 
         CONVERT(BIT, 0) AS [isSelected] 
INTO #Temp1 
FROM Sys.Columns col 
JOIN Sys.types typ 
ON col.[user_type_id] = typ.[user_type_id] 
WHERE object_name(object_id) = 'tblActor'