Post reply

t-sql 2012 insert into a temp table

  • December 12, 2019 at 7:26 pm

    #3707505

    Can you show me a better way to place hardcoded values into a sql server 2012 temp table with selected values from a table called HTeacher and a 'static' values?

    How would I change the sql below?

    create table #HomeroomTeacher2 (TeacherPersonID int , sectionTeacherDisplay varchar(105))

    insert into #HomeroomTeacher2 (TeacherPersonID , sectionTeacherDisplay )

    values (0, 'No Hm Teacher' )

    create table #HomeroomTeacher3 (TeacherPersonID int , sectionTeacherDisplay varchar(105))

    insert into #HomeroomTeacher3

    select TeacherPersonID, sectionTeacherDisplay from #HomeroomTeacher2

    union all

    SELECT DISTINCT TeacherPersonID, sectionTeacherDisplay

    from HTeacher

    ORDER BY TeacherPersonID, sectionTeacherDisplay

    select * from #HomeroomTeacher3

    DROP TABLE #HomeroomTeacher2

    drop table #HomeroomTeacher3

  • December 12, 2019 at 9:44 pm

    #3707523

    This only requires one temp table, which I think is what you are looking for.

    create table #HomeroomTeacher3 (TeacherPersonID int , sectionTeacherDisplay varchar(105))

    insert into #HomeroomTeacher3

    SELECT TeacherPersonID, sectionTeacherDisplay
    from HTeacher

    UNION

    SELECT TeacherPersonID, sectionTeacherDisplay
    FROM (values (0, 'No Hm Teacher' )) t(TeacherPersonID, sectionTeacherDisplay)
    ORDER BY TeacherPersonID, sectionTeacherDisplay

    Also, I changed your query a little.  You're doing a DISTINCT with a UNION ALL.  It's certainly easier and probably marginally faster to just use UNION.

    Drew

    • This reply was modified 4 years, 4 months ago by  drew.allen. Reason: Added missing paren

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 12, 2019 at 9:48 pm

    #3707526

    Actually, why don't you just insert it into your first temp table instead of creating a second?

    create table #HomeroomTeacher2 (TeacherPersonID int , sectionTeacherDisplay varchar(105))

    insert into #HomeroomTeacher2 (TeacherPersonID , sectionTeacherDisplay )
    values (0, 'No Hm Teacher' )

    insert into #HomeroomTeacher2
    SELECT DISTINCT TeacherPersonID, sectionTeacherDisplay
    from HTeacher
    ORDER BY TeacherPersonID, sectionTeacherDisplay

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply