Insert Data Into temporary table using Select * Into

Question

I want to insert data into temporary table for that i am using select * into syntax.

But i am getting error :

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

Though Single Query getting executed successful.

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

• Ankit: You don't use existing table for Select * INTO. Delete that table and try executing the query again. Or you need to use Insert Into statement

Answer 1

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

Answer 2

As @Raj said you either use Create or select into.

However he missed as ALIAS_NAME. It is as below :

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

• Raj: Good catch. I actually copied the code and changed Select into to Insert into. Did not test the code.