December 12, 2019 at 7:26 pm
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
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 12, 2019 at 9:48 pm
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