SELECT INTO #temporary-tables bottlenecks in SQL Server 6.5 and 7

Early this week, I stumbled into a puzzling error.

I created a temporary table in a store procedure, exec an sql statement, and select * records in that temporary table. Code pasted below

CREATE TABLE #TmpCareModel
(
EpisodeID INT ,
EventDate VARCHAR(12),
ID INT,
EventCareModel INT
)

SELECT @SQL = ‘SELECT EpisodeID, MAX(Convert(Datetime, EventDate,103)) MaxDate, ID, EventCareModel INTO #TmpCareModel
FROM ObAnteEvent WHERE EventType = ”CareModel” AND EventCareModel = EventCareModel AND EventDate <> ”” GROUP BY EpisodeID, ID, EventCareModel ‘

EXEC (@SQL)
SELECT * FROM #TmpCareModel

Simple straight-forward case. However, during execution there are no records returned (although it is supposed to) and when I execute it in Query manager (cut and paste the same sql) there are records returned.

I suspected it was something to do with ‘SELECT INTO #temporary-table’ and googled for an answer. Neil Boyle wrote an article about avoiding temporary table bottlenecks and mentioned the implications of writing a direct SELECT INTO #temporary-table in SQL Server version 6.5 and 7

When I tried his solution, it worked! Well, here’s the quote from his article

create table #temp( ........ ) insert #temp select * from sourceTable