set nocount on

create table studentList
(studentID varchar(10), [name] varchar(10), classID varchar(5))
insert studentList
select '01001', 'A', '01'
union all select '01002', 'B', '01'
union all select '01003', 'C', '01'
union all select '01004', 'D', '01'
union all select '02001', '02D', '02'
union all select '02002', '02E', '02'
union all select '02003', '02F', '02'
union all select '02004', '02G', '02'

create table courseList
(serialNo int identity(1,1), studentID varchar(10), [name] varchar(10), classID varchar(5),
courseID varchar(10))

insert courseList(studentID, [name], classID, courseID)
select '01001', 'A', '01', '300435'
union all select '01001', 'A', '01', '10110'
union all select '01001', 'A', '01', '201121'
union all select '02001', '02D', '02', '40014'
union all select '02001', '02D', '02', '300435'
union all select '01002', 'B', '01', '300435'
union all select '01002', 'B', '01', '10110'
union all select '01002', 'B', '01', '201121'
GO

with courseCTE (classID, courseID)
AS
(
    select distinct classID, courseID
    from courseList
)

INSERT courseList
(studentID, [name], classID, courseID)
select sl.studentID, sl.[name], sl.classID, c.courseID
from studentList sl
JOIN courseCTE c
ON sl.classID = c.classID
Where NOT Exists (select 1 from courseList where studentID = sl.studentID AND courseID = c.courseID)
order by [name]
GO

select * from courseList
GO

drop table studentList
drop table courseList
Go

------解决方案--------------------
要安装Analysis Services的吧