0

When executing the query below, I am receiving the error: There is already an object named '##tempTable' in the database. I am using a temporary table to insert the results and do other things not important to the question, but I need this if statement because there are a few minor differences in the queries and I don't know how to consolidate into 1 query. The error occurs in the else statement SELECT... INTO ##tempTable because I already perform this operation in the if. Is there any work around to this besides getting rid of the if else statement and consolidatin into one query?

IF @isQuintile = 1
            BEGIN
                SELECT MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID, 0 AS 'fkDemographicCodeID', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_0', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) AS 'Count_0', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '0') AS 'BandID_0', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_1', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) AS 'Count_1', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '1') AS 'BandID_1' 
                 INTO ##tempTable FROM StudentScores_Subject
                        INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
                        INNER JOIN TestInstances ON  TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
                        INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID   
                        INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                        LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
                            AND cutScores.fkGradeID = @intGradeId
                            AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
                        INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
                            AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue) 
                            OR ((CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN bandComponents.minScore and bandComponents.maxScore)
                            OR (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN cutScores.minScore and cutScores.maxScore)))
                        RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
                WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
                        AND bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                        AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
                        AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
                        AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
                GROUP BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
                ORDER BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
            END
            ELSE
            BEGIN
                SELECT MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID, 0 AS 'fkDemographicCodeID', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_0', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) AS 'Count_0', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '0') AS 'BandID_0', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_1', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) AS 'Count_1', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '1') AS 'BandID_1' 
                INTO ##tempTable FROM StudentScores_Subject
                        INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
                        INNER JOIN TestInstances ON  TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
                        INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID   
                        INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                        LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
                            AND cutScores.fkGradeID = @intGradeId
                            AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
                        INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
                            AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue))
                        RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
                WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
                        AND bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                        AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
                        AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
                        AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
                GROUP BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
                ORDER BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID              

            END

EDIT Should have specified, but the reason I am selecting INTO is because the select is created dynamically so I don't know how many columns there will be. In my example I just showed what it would look like if there was only count, percent, and band 0 and 1 selected.

frontin
  • 733
  • 2
  • 12
  • 28
  • 2
    That isn't a temp table. It is a global temp table. The big difference is the global version can be seen by any connection. Change your table name to have a single # instead of two. – Sean Lange Dec 09 '14 at 17:57
  • @SeanLange I still get the same error, but now with #tempTable. – frontin Dec 09 '14 at 18:08
  • 1
    The reason for the error is because you are trying to use select into with the same table name from both sides of the if statement. That will not compile because it looks like the table is being created twice. It looks like your two queries are almost identical (I can't see a difference to be honest). You could probably do this as a single select into instead of two huge queries like this. – Sean Lange Dec 09 '14 at 18:16

3 Answers3

0

You need to Check Some thing like this Before Creating Temporary Tables

if OBJECT_ID(''tempdb..##tempTable) is not null
drop table ##tempTable

Then Your

SELECT * INTO ##tempTable......
Dgan
  • 10,077
  • 1
  • 29
  • 51
0

##tempTable(Global temporary table) and #temptable(local Temporary table)

See the difference.. Local and global temporary tables in SQL Server

create table #tempTable(declare the columns here..)

IF @isQuintile = 1
            BEGIN
                SELECT MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID, 0 AS 'fkDemographicCodeID', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_0', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) AS 'Count_0', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '0') AS 'BandID_0', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_1', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) AS 'Count_1', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '1') AS 'BandID_1' 
             INTO #tempTable FROM StudentScores_Subject
                    INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
                    INNER JOIN TestInstances ON  TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
                    INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID   
                    INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                    LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
                        AND cutScores.fkGradeID = @intGradeId
                        AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
                    INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
                        AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue) 
                        OR ((CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN bandComponents.minScore and bandComponents.maxScore)
                        OR (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN cutScores.minScore and cutScores.maxScore)))
                    RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
            WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
                    AND bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                    AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
                    AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
                    AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
            GROUP BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
            ORDER BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
        END
        ELSE
        BEGIN
            SELECT MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID, 0 AS 'fkDemographicCodeID', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_0', SUM(CASE WHEN bands.StackPosition = '0' THEN 1 ELSE 0 END) AS 'Count_0', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '0') AS 'BandID_0', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) * 100.0/ CASE WHEN COUNT(pkStudentScoreID) = 0 THEN 1 ELSE COUNT(pkStudentScoreID) END AS 'Percent_1', SUM(CASE WHEN bands.StackPosition = '1' THEN 1 ELSE 0 END) AS 'Count_1', (SELECT bb.pkPerformanceLevelReportBandID FROM PerformanceLevelReportBands bb WHERE bb.fkPerformanceLevelReportID = '6' AND bb.StackPosition = '1') AS 'BandID_1' 
            INTO #tempTable FROM StudentScores_Subject
                    INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
                    INNER JOIN TestInstances ON  TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
                    INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID   
                    INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                    LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
                        AND cutScores.fkGradeID = @intGradeId
                        AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
                    INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
                        AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue))
                    RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
            WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
                    AND bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                    AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
                    AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
                    AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
            GROUP BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID
            ORDER BY MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, StudentScores_Subject.fkTest_SubjectID              

        END
Community
  • 1
  • 1
A_Sk
  • 4,532
  • 3
  • 27
  • 51
  • create table #tempTable(pkSchoolYearID bigintint,pkTestInstanceID bigint, etc etc..) i don't know how to edit ..and i can't see clery the full select statement. i'm new here for answering any question. – A_Sk Dec 09 '14 at 18:33
  • And i think he'll understand the declaration, because he already declared the Global temporary table.. – A_Sk Dec 09 '14 at 18:35
0

You don't need to use SELECT INTO...

Just create the temp table (local or global) first and the do an INSERT INTO...SELECT:

IF (OBJECT_ID(N'tempdb..#tempTable') IS NOT NULL)
BEGIN
  DROP TABLE #tempTable;
END;

CREATE TABLE #tempTable (
   pkSchoolYearID datatype,
   pkTestInstanceID datatype,
   fkTest_SubjectID datatype,
   fkDemographicCodeID INT
   Percent_0 datatype
   Count_0 datatype,
   BandID_0 datatype,
   Percent_1 datatype,
   Count_1 datatype,
   BandID_1 datatype
);

IF (@isQuintile = 1)
BEGIN
    INSERT INTO #tempTable -- this has been added!!
       SELECT MM_SchoolYears.pkSchoolYearID, TestInstances.pkTestInstanceID, ...
       -- INTO ##tempTable -- this has been removed!!
       INNER JOIN StudentTests
               ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
       ...
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171