SELECT INTO #temporary-tables bottlenecks in SQL Server 6.5 and 7
Posted by scmay on August 23, 2007
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
Technical/Business Aspects in IT 
yudz said
as far as I know, you cant use select into when the table is already exists. select into is used for creating table automatically and also insert the data.
And also, when u use dynamic sql, and exec it, you cant not select the temporary table outside the exec, since it is in different ‘session’…
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
SELECT * FROM #TmpCareModel‘
EXEC (@SQL)
This hope works :), CMIIW