t-sql 2012 insert into a temp table

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  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

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  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 5 years, 6 months ago by drew.allen. Reason: Added missing paren

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  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