SQL脚本来填充数据库 [英] SQL script to populate database

问题描述

我正在尝试编写一个填充数据库的脚本.
有人告诉我我需要使用临时表和游标编写脚本.
说明是使用临时表将输入"存储到脚本中，然后使用游标迭代这些输入，并在不存在的数据库中生成记录.
我知道如何使用临时表，但是我对游标没有足够的了解.
如果不存在任何年份，我需要此脚本在数据库中创建学校，资格证书和课程.凭条还应该在正确的资格范围内对课程进行索引，并且还应该在正确的学校范围内对资格进行索引.

有人可以帮我吗?

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;

推荐答案

我有一个小模板，我在需要时就做了光标.

您必须根据需要进行调整:

I have small template I made a while ago for whenever I need a cursor.

You have to adjust it to Your needs:

DECLARE @VARA int
DECLARE @VARB varchar(2)
DECLARE @VARC datetime

DECLARE MyPointer CURSOR FOR SELECT A, B, C FROM MyTable
OPEN MyPointer
-- Find first record
FETCH NEXT FROM MyPointer INTO @VARA, @VARB, @VARC
-- Check @@FETCH_STATUS for more records
WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Do something here with @VARA, @VARB, @VARC

        FETCH NEXT FROM MyPointer INTO @VARA, @VARB, @VARC
    END
CLOSE MyPointer
DEALLOCATE MyPointer

如果情况如此，请不要忘记将其标记为已回答.

Don''t forget to mark it as answered if it''s the case.

这篇关于SQL脚本来填充数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！