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

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 — Ankit
– Ankit, Commented Feb 18, 2013 at 11:28

Raj · Accepted Answer · 2013-02-18 11:24:49Z

2

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

edited Feb 18, 2013 at 11:24

answered Feb 18, 2013 at 11:05

Raj

10.9k2 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi Singh · Accepted Answer · 2013-02-18 11:21:28Z

1

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

edited Feb 18, 2013 at 11:21

answered Feb 18, 2013 at 11:15

Ravi Singh

2,08013 silver badges29 bronze badges

1 Comment

Raj Over a year ago

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

Collectives™ on Stack Overflow

Insert Data Into temporary table using Select * Into

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related