Create a temporary table from a subquery in sql server causing error
20,079
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
Author by
RookieAppler
Updated on July 12, 2022Comments
-
RookieAppler almost 2 years
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 errorMsg 2714, Level 16, State 6, Line 55
There is already an object named '#TempHourlyTable' in the database. -
RookieAppler over 10 yearsThanks. I got through your approach. Although i have a new question. I edited my current question. Can you take a look at it?Thanks
-
RookieAppler over 10 yearsThanks. I have a new question. I edited my current question. Can you take a look at it?
-
nathan_jr over 10 yearsNeed another dot: object_id('tempdb..#TempHourlyTable')
-
Hilary almost 8 yearsfor 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!