CODEWITHSUNDEEP
sqlsql-server-2012subquerytemp-tables
RookieAppler
RookieAppler

Reputation: 1545

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

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.

Upvotes: 3

Views: 25957

Answers (2)

Ishey4
Ishey4

Reputation: 327

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

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9282

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

Upvotes: 7

Related Questions