Select Into Temp Table In Stored Procedure
Posted on 2006-04-24
I have a stored procedure that uses a series of Selects to get down to a specific grouping of data. I am now attempting to stick the result set into a temp table, in order to combine it with other selections. The problem is that the Select Into does not seem to work. As you'll see below, I can manually insert one record, and have it return successfully, but not stick a set of a data into it.
Testing further, even if my Select only returns a single line, the problem persists.
CREATE TABLE #OT(
[Total Hours] float,
[Approved Hours] float,
[Over Time] float,
[Approved OT] float
INSERT INTO #OT values('Test Guy',9.9,9.9,9.9,9.9,9.9)
EXEC('SELECT A.FullName as [Employee], A.[Total Hours], A.[Approved Hours], (A.[Total Hours]-A.[Approved Hours]) as [Discrepancy], (A.[Total Hours]-' + @otHours + ') as [Over Time], (A.[Approved Hours]-' + @otHours + ') as [Approved OT] INTO #OT FROM (SELECT FullName, sum(totHours) as [Total Hours], sum(AA.appMon + AA.appTues + AA.appWed + AA.appThurs + AA.appFri + AA.appSat + AA.appSun) as [Approved Hours] FROM (' + @sum1 + ' UNION ' + @sum2 + ' UNION ' + @sum3 + ' UNION ' + @sum4 + ' UNION ' + @sum5 + ' UNION ' + @sum6 + ' UNION ' + @sum7 + ' UNION ' + @statement1 + ') as AA GROUP BY FullName) as A');
SELECT * FROM #OT;