large Multi-column insert to temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

large Multi-column insert to temp table

Author

Topic

ngk
Starting Member

10 Posts

Posted - 2008-10-14 : 05:47:41

Hi there I received the following err for a query:
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes.....
This is a select that returns lots of aggregated columns(sums) & was doing a Group By on a non-indexed column from a joined table.

To combat this I thought I'd return the columns to a temp table & then do the join on the temp table

I create temp table as so:
Declare @Script as Varchar(8000);

Declare @Script_prepare as Varchar(8000);

Declare @i as int;
Declare @col as varchar(6);

CREATE TABLE #CMTemp(DeptRef NVARCHAR(50)) -- Creating Temp Table

Set @i = 1

WHILE @i < 100
begin
DECLARE @DynamicSQL VARCHAR(500)

set @col = 'col' + CAST(@i AS NVARCHAR(3))

IF NOT EXISTS ( SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('#CMTemp') AND NAME = @col )

SET @DynamicSQL = 'ALTER TABLE #CMTemp ADD ['+CAST(@col AS NVARCHAR(100))+'] NVARCHAR(100) NULL'
EXECUTE (@DynamicSQL)
SET @i = @i+1
end

Thinking that the insert from the select would be straight forward
the Select is in the form :

SELECT DeptRef, SUM (CASE WHEN Account='42425' THEN IsNull(Amount,0) ELSE 0 END) AS [ R&D] , SUM (CASE WHEN Account='N/A' THEN IsNull(Amount,0) ELSE 0 END) AS [<TBR>] , SUM (CASE WHEN Account='62055' THEN IsNull(Amount,0) ELSE 0 END) AS [62055] , SUM (CASE WHEN Account='02222' THEN IsNull(Amount,0) ELSE 0 END) AS [02222] , .....

How can I do this differently so that I can create the columns with the names as they are aliased without having to list them all out & do an insert to the temp table in the same way - is this possible? Such as a wildcard way of creating them.. I hope I've explained this clearly!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 05:57:59

[code]SELECT DeptRef,
SUM(CASE WHEN Account = '42425' THEN ISNULL(Amount, 0) ELSE 0 END) AS [R&D],
SUM(CASE WHEN Account = 'N/A' THEN ISNULL(Amount, 0) ELSE 0 END) AS [<TBR>],
SUM(CASE WHEN Account = '62055' THEN ISNULL(Amount, 0) ELSE 0 END) AS [62055],
SUM(CASE WHEN Account = '02222' THEN ISNULL(Amount, 0) ELSE 0 END) AS [02222]
INTO #CMTemp
FROM Table1
GROUP BY DeptRef[/code]

E 12°55'05.63"
N 56°04'39.26"