Hi,
I am trying to write a script which populates a database.
I have been told I need to use temporary tables and cursors to write the script.
The instructions are to use a temporary table to store the "inputs" to your script, then use a cursor to iterate over those inputs and to generate records in the database if they don't exist.
I understand how to use temporary tables, however I don't have a firm understanding of cursors.
I need this script to create the schools, qualifications and courses in the database, for any given year if they don't exist. The scrip should also index the courses within the correct qualification and also the qualification should be indexed within the correct school.
Can someone please help me here?
SET NOCOUNT ON;
DECLARE @year int;
SET @year='2013';
create table #mock(facultyTitle varchar(64), qualificationTitle varchar(64), Qoccurs varchar(64), courseTitle varchar(128), courseCode varchar (64), Coccur varchar(64));
INSERT INTO #mock(facultyTitle, qualificationTitle, Qoccurs, courseTitle, courseCode, Coccur) VALUES ('Art and Design', 'Design, 2013', '0= whole year', 'Creative Process', 'CP', '0= whole year');
INSERT INTO #mock(facultyTitle, qualificationTitle, Qoccurs, courseTitle, courseCode, Coccur) VALUES ('Art and Design', 'Design, 2013', '0= whole year', 'Core Theory Art and Design', 'CTAD', '1= first semester');
INSERT INTO #mock(facultyTitle, qualificationTitle, Qoccurs, courseTitle, courseCode, Coccur) VALUES ('Art and Design', 'Design, 2013', '0= whole year', 'History, Culture, Context', 'HCC', '1= first semester');
INSERT INTO #mock(facultyTitle, qualificationTitle, Qoccurs, courseTitle, courseCode, Coccur) VALUES ('Art and Design', 'Design, 2013', '0= whole year', 'Digital Technology and Design', 'DTD', '2= second semester');
SELECT #mock.facultyTitle, #mock.qualificationTitle, #mock.Qoccurs, #mock.courseTitle, #mock.courseCode, #mock.Coccur, school.schoolID, qualification.qualificationID, course.courseID
INTO #mockdata2
FROM #mock
LEFT JOIN school ON #mock.facultyTitle=school.name COLLATE Latin1_General_CI_AS
LEFT JOIN qualification ON #mock.qualificationTitle=qualification.title COLLATE Latin1_General_CI_AS
LEFT JOIN course ON #mock.courseTitle=course.title COLLATE Latin1_General_CI_AS AND #mock.courseCode=course.code COLLATE Latin1_General_CI_AS
IF EXISTS(SELECT 1 FROM #mockdata2 WHERE schoolID IS NULL)
BEGIN
print 'At least one school can not be found';
select DISTINCT facultyTitle from #mockdata2 WHERE schoolID IS NULL
END
IF EXISTS(SELECT 1 FROM #mockdata2 WHERE qualificationID IS NULL)
BEGIN
print 'At least one qualification can not be found';
select DISTINCT qualificationTitle from #mockdata2 WHERE qualificationID IS NULL
END
IF EXISTS(SELECT 1 FROM #mockdata2 WHERE courseID IS NULL)
BEGIN
print 'At least one course can not be found';
select DISTINCT courseTitle, courseCode from #mockdata2 WHERE courseID IS NULL
END
DECLARE @errorCount int
DECLARE @schoolID int
DECLARE @qualificationID int
DECLARE @courseID int
SET @errorCount=0
DECLARE schoolCursor CURSOR FOR
SELECT DISTINCT schoolID, qualificationID, courseID FROM #mockdata2;
OPEN schoolCursor;
FETCH NEXT FROM schoolCursor INTO @schoolID, @qualificationID, @courseID;
WHILE @@FETCH_STATUS = 0
BEGIN;