SQL script to populate database

See more:

SQL-server-2000

SQL-Server-2005

, +

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;

/***** START OF INPUTS *****/

DECLARE @year int;
SET @year='2013';

--list of schools and qualifications...
create table #mock(facultyTitle varchar(64), qualificationTitle varchar(64),  Qoccurs varchar(64), courseTitle varchar(128), courseCode varchar (64), Coccur varchar(64));

--repeat the below for each school, qualification and course
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');
--Note: not all insert statements are included

/***** END OF INPUTS *****/

--check that the schools, qualifications and courses we have are valid...
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


--check that all the schools can be found, message should come as school shouldn't exit 
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

--check that all the qualifications can be found, message should come as qualification shouldn't exit 
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

--check that all the courses can be found, message should come as course shouldn't exit 
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

--Check that the schools we have been given exist in database
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;