tsql temp table issue 1

[bouwob]

So I have this sql

declare @tempstr varchar(8000)
declare @freds varchar(3000)

set @freds = '8347151, 4, 7608501'



set @tempstr = 'create table temp as select fredid, bubbid, count(dotid) from freds where fredid in (' + @freds + ')'

exec (@tempstr)

but am recieving this error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

it hovers over "declare @tempstr varchar(8000)" when I click on the error message.

Can I build a temp table in tsql and if so How do I do it???

 

[xpblueScreenOfDeath]

You must put the symbol "#" infront of all temporary tables.

Try this:

declare @tempstr varchar(8000)
declare @freds varchar(3000)

set @freds = '8347151, 4, 7608501'

set @tempstr =  'select fredid, bubbid, count(dotid) into #temp from freds where fredid in (' + @freds + ')'

exec (@tempstr)

or

declare @tempstr varchar(8000)
declare @freds varchar(3000)

Create table #temp
(
fredid int,
bubbid int,
numRecs int
)

set @freds = '8347151, 4, 7608501'

set @tempstr =  'Insert into #temp(fredid, bubbid, numRecs)select fredid, bubbid, count(dotid) from freds where fredid in (' + @freds + ')'

exec (@tempstr)