SQL 使用 Select * Into 将数据插入临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14934740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data Into temporary table using Select * Into
提问by Shaggy
I want to insert data into temporary table for that i am using select * intosyntax.
我想将数据插入到临时表中，因为我正在使用select * into语法。
But i am getting error :
但我收到错误：
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
Though Single Query getting executed successful.
虽然 Single Query 执行成功。
Code:
代码：
Create Table #_Total
(
A Int,
B Int,
C Int,
D Int
)
Select * InTo #_Total From(
Select
Sum(Case When Closed=0 And ISNULL(VendorTicketNo,'')='' Then 1 Else 0 End),
Sum(Case When Closed=1 And TicketType<>8 AND ISNULL(VendorTicketNo,'')<>'' Then 1 Else 0 End),
Sum(Case When Closed=1 And CAST(ClosedOn As DATE)= CONVERT(VARCHAR(8),GETDATE(),112) Then 1 Else 0 End),
Sum(Case When Closed=0 And TicketType=8 Then 1 Else 0 End)
From ALBATMStatus.dbo.Ticket
)
Select * From #_Total
Database - SQL SERVER 2008
数据库 - SQL SERVER 2008
回答by Raj
Since you are creating the table before hand, you have to use
由于您是事先创建表，因此您必须使用
INSERT INTO
Try this
尝试这个
Create Table #_Total
(
A Int,
B Int,
C Int,
D Int
)
Insert Into #_Total
Select * From(
Select
Sum(Case When Closed=0 And ISNULL(VendorTicketNo,'')='' Then 1 Else 0 End),
Sum(Case When Closed=1 And TicketType<>8 AND ISNULL(VendorTicketNo,'')<>'' Then 1 Else 0 End),
Sum(Case When Closed=1 And CAST(ClosedOn As DATE)= CONVERT(VARCHAR(8),GETDATE(),112) Then 1 Else 0 End),
Sum(Case When Closed=0 And TicketType=8 Then 1 Else 0 End)
From ALBATMStatus.dbo.Ticket
) AS a
Select * From #_Total
Raj
拉吉
回答by Ravi Singh
As @Raj said you either use Createor select into.
正如@Raj 所说，您要么使用Create或select into.
However he missed as ALIAS_NAME. It is as below :
然而他错过了as ALIAS_NAME。如下：
Create Table #_Total
(
A Int,
B Int,
C Int,
D Int
)
Insert Into #_Total
Select * From(
Select
Sum(Case When Closed=0 And ISNULL(VendorTicketNo,'')='' Then 1 Else 0 End) A,
Sum(Case When Closed=1 And TicketType<>8 AND ISNULL(VendorTicketNo,'')<>'' Then 1 Else 0 End) B,
Sum(Case When Closed=1 And CAST(ClosedOn As DATE)= CONVERT(VARCHAR(8),GETDATE(),112) Then 1 Else 0 End) C,
Sum(Case When Closed=0 And TicketType=8 Then 1 Else 0 End) D
From ALBATMStatus.dbo.Ticket
) as q1
Select * From #_Total
