10春数据库导入方法.doc

1、 新教务库导入过程（1） 导入新版教务的学生数据到SUBEDU数据库中，通过数据库的“数据导入/导出”方式将导入新生数据所需要的数据表从新版教务数据库PRTVU中导入到教学平台数据库Subedu中，要导入到Subedu库中的表有两个：XSB、XSJBQKB，并保持表名不变；如果这两个表已经在Subedu中存在则：或者在导入前先将其删除，或者在数据导入时在“编辑映射”中选择“删除目标表中的行”选项。（2） 从新教务的PRTVU数据库中导入必要的数据表到SUBEDU数据库中，需要从新教务数据库中导入的数据表有：1、xxdmb（学校代码表）、2、kczb（课程总表）、3、zydmb（专业代码表）、4、zygzb（专业规则表）、5、mkkcb（模块课程表）、6、zygzsyndb（专业规则适应年度表）共计6个表。数据的导入可通过数据库的“数据导入/导出”方式进行，如果在SUBEDU库中已经存在要导入的表则有两种操作方式：1）可以在导入数据前将上面提到的6个表删除；或者2）在选择好要导入的6个表后，分别选中每个表，在每个表的“编辑映射”中选择“删除目标表中的行”。在进行上述操作后，即可进行数据的导入操作。（3） 执行下方的sql语句（复制后，在查询分析器粘贴，点执行查询语句或按F5）。USE SUBEDUGO-导入新增学校信息-INSERT INTO dbo.Province_Info_T (keyID,keyValue)SELECT RTRIM(Xxdm),Xxmc FROM dbo.xxdmb WHERE RTRIM(Xxdm) NOT IN (SELECT RTRIM(keyID) FROM dbo.Province_Info_T)GO-导入新增加的教务课程信息-INSERT INTO dbo.Course_Info_T (CourseID,CourseDesc,CourseName)SELECT Kcid,新教务课程,KcmcFROM dbo.kczb WHERE Kcid NOT IN (SELECT CourseID FROM dbo.Course_Info_T)GO-导入新增专业规则信息- 作为教学计划处理，计划名称为：专业规则制定的年份+ 专业规则制定的学期+ 专业名称- 在教学计划表中，将Grade字段值置为：作为新教务中专业规则的标识INSERT INTO subedu.dbo.Plan_Info_T (PlanID,MajorID,Grade,Dwdm ,PlanDesc ,PlanName ,EduSystem,Period,FinishCount,MustCount ,IsClose ,StudentType ,StudentClass)SELECT dbo.zygzb.Gzh,dbo.zygzb.Zydm,0000,010,dbo.zygzb.Zdnd + 年 + CASE dbo.zygzb.Zdxq WHEN 03 THEN 春 ELSE 秋 END + 制定的新教务专业规则,dbo.zygzb.Zdnd + 年 + CASE dbo.zygzb.Zdxq WHEN 03 THEN 春 ELSE 秋 END + dbo.zydmb.Zymc,dbo.zygzb.Xzdm,dbo.zygzb.Xzdm,dbo.zygzb.Byxf,dbo.zygzb.Zyxf,CASE dbo.zygzb.Qybz WHEN 1 THEN N ELSE Y END,CASE dbo.zygzb.Zyccdm WHEN 2 THEN 1 ELSE 7 END,dbo.zygzb.XslxdmFROM dbo.zygzb INNER JOIN dbo.zydmb ON dbo.zygzb.Zydm=dbo.zydmb.ZydmWHERE dbo.zygzb.Gzh NOT IN (SELECT PlanID FROM dbo.Plan_Info_T)-导入专业规则-课程关系信息-INSERT INTO dbo.Plan_Course_All_T (PlanID,CourseID,KCXZ ,KCLB,StudyCount,StartTerm ,IsClose)SELECT Gzh,Kcid,CASE Kclxdm WHEN 1 THEN 6 WHEN 2 THEN 7 ELSE Kclxdm END,CASE Kcxzdm WHEN 2 THEN 8 ELSE Kcxzdm END,Xf,Ksxq,CASE RTRIM(Kczt) WHEN 0 then N else Y endFROM dbo.mkkcbWHERE Gzh + _ + Kcid NOT IN (SELECT PlanID + _ + CourseID FROM dbo.Plan_Course_All_T)-导入开设的教学计划课程对照信息(只导入开设的课程信息）-delete from Plan_Course_Tinsert into Plan_Course_T(PlanID, CourseID, KCXZ,KCLB, StudyCount, StartTerm)select PlanID,CourseID,KCXZ,KCLB,StudyCount,StartTermfrom Plan_Course_All_T where IsClose=N-导入专业规则适应年度表-INSERT INTO subedu.dbo.NewJw_Rule_Used_Info (Gzh,Zydm,Nd,Xqdm,Qybz ,Zyccdm ,Zymc)SELECT dbo.zygzsyndb.Gzh,dbo.zygzsyndb.Zydm,dbo.zygzsyndb.Nd,dbo.zygzsyndb.Xqdm,dbo.zygzsyndb.Qybz,CASE dbo.zygzsyndb.Zyccdm WHEN 2 THEN 1 ELSE 7 END,dbo.zygzsyndb.Nd + 年 + CASE dbo.zygzsyndb.Xqdm WHEN 03 THEN 春 ELSE 秋 END + dbo.zydmb.ZymcFROM dbo.zygzsyndb INNER JOIN dbo.zydmb ON dbo.zygzsyndb.Zydm=dbo.zydmb.ZydmWHERE dbo.zygzsyndb.Gzh + dbo.zygzsyndb.Zydm + dbo.zygzsyndb.Nd + dbo.zygzsyndb.Xqdm NOT IN (SELECT Gzh + Zydm + Nd + Xqdm FROM dbo.NewJw_Rule_Used_Info)-更新教学计划信息-insert into Plan_Course_T(PlanID, CourseID, KCXZ,KCLB, StudyCount, StartTerm)select PlanID,CourseID,KCXZ,KCLB,StudyCount,StartTermfrom Plan_Course_All_T where IsClose=N and CAST(PlanID AS varchar) +=+ CAST(CourseID AS varchar)not in (select CAST(d.PlanID AS varchar)+=+CAST( d.CourseID AS varchar) from Plan_Course_T d)-如果以上语句执行准确，可采用以下语句将导入的新教务表删除，只需将注释符-删除即可-DROP TABLE dbo.xxdmb-DROP TABLE dbo.kczb-DROP TABLE dbo.zydmb-DROP TABLE dbo.zygzb-DROP TABLE dbo.mkkcb-DROP TABLE dbo.zygzsyndb上述语句执行完毕之执行以下语句（增加课程栏目）declare CourseID Int declare CourseName varChar(100) declare NextFirst varChar(1) declare NextSecond varChar(1) /* 声明游标 */declare toptopic cursorfor SELECT DISTINCT A.CourseID,b.CourseName FROM Plan_Course_T A INNER JOIN Course_Info_T B ON A.CourseID=B.CourseID WHERE A.CourseID Not IN (SELECT Distinct CourseID FROM Topic_Code_T) ORDER BY A.CourseIDdeclare TheTopicCode varChar(32)-set TheTopicCode = (select Top 1 substring(TopicCode,1,2) from Topic_Code_T where TopicCode like _ order by TopicCode Desc)/* 打开游标 */open toptopicfetch next from toptopic into CourseID,CourseNameWHILE FETCH_STATUS = 0BEGIN/* 获得栏目代码注意，没有考虑代码不在09a-z之外的情况*/-set NextFirst = Substring(TheTopicCode,1,1)-set NextSecond = Substring(TheTopicCode,2,1)/* 获得第二个字符 */*IF NextSecond = 9set NextSecond = AElseBeginIF NextSecond = ZBeginset NextSecond = 0- 获得第一个字符 IF NextFirst = 9set NextFirst = AElseBeginset NextFirst = Char(Ascii(NextFirst)+1)EndEndElseset NextSecond = Char(Ascii(NextSecond)+1)End*/* 组合得到栏目代码 */-set TheTopicCode = NextFirst + NextSecondset TheTopicCode = A0/*添加一级栏目*/insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode,2,0,1002867999,1002867999,CourseName,CourseID,root)/*添加二级栏目*/insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+00,2,0,1002867999,1002867999,课程说明,CourseID,root)insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+01,2,0,1002867999,1002867999,教师介绍,CourseID,root)insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+02,2,0,1002867999,1002867999,教学大纲,CourseID,root)insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+03,2,0,1002867999,1002867999,实施方案,CourseID,root)insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+04,2,0,1002867999,1002867999,教学辅导,CourseID,root)insert into Topic_Code_T (TopicCode,PubClass,IsDeleted,DateCreated,DateUpdated,TopicName,CourseID,UserUpdated) values(TheTopicCode+05,2,0,1002867999,1002867999,直播课堂,Cours