0
Okay I figuered it out, here is the correct code to make this wrok:
SELECT * INTO #tempAgentStatus2 from (SELECT UserId, COUNT(*) as a
FROM #tempAgentStatus
GROUP BY UserId)a
I was missing the "as a" after "COUNT(*). In case anyone else runs into this problem.