Create a temporary table from a subquery in sql server causing error

Question

I have something like this

 if object_id('tempdb.#TempHourlyTable') is not null
drop table #TempHourlyTable

 select * into #TempHourlyTable
 from (
         select top 10 * from customers
      )

I get an error like this:

Incorrect syntax near ')'.

My first attempt with temporary tables. So what is the mistake here?

EDIT:
Drop and recreate if temporary table exists. Getting error

Msg 2714, Level 16, State 6, Line 55
There is already an object named '#TempHourlyTable' in the database.

nathan_jr · Accepted Answer · 2014-02-21 21:23:00Z

7

You need to alias your derived table (subquery), like:

select * into #TEmpTable
 from (
         select top 10 * from customers
      ) as [SomeAlias]

You can also:

select top 10 * 
into #temp
from customers

answered Feb 21, 2014 at 21:23

nathan_jr

9,3023 gold badges43 silver badges55 bronze badges

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

3 Comments

RookieAppler Over a year ago

Thanks. I got through your approach. Although i have a new question. I edited my current question. Can you take a look at it?Thanks

nathan_jr Over a year ago

Need another dot: object_id('tempdb..#TempHourlyTable')

Hilary Over a year ago

for added info ... it doesn't work without the alias even though alias not necessarily referenced in the select-into. (I tried leaving it out in the interests of streamlined coding). I'll know better next time!

Ishey4 · Accepted Answer · 2014-02-21 21:24:30Z

0

try this

insert into #TEmpTable 
select top (10) * from customers

what are you looking to do with the temp. table?

i find using CTEs much more convenient

CTEs

answered Feb 21, 2014 at 21:24

Ishey4

3273 silver badges13 bronze badges

1 Comment

RookieAppler Over a year ago

Thanks. I have a new question. I edited my current question. Can you take a look at it?

Collectives™ on Stack Overflow

Create a temporary table from a subquery in sql server causing error

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related