旧版数据导入新版数据方法.doc

2010.01.17 龙岗数据转新版数据旧数据库类型A旧数据库类型B先备份新旧数据库，然后升级1. DEPART - tbDepartment31-0其它不变改表与DOCTORUSER关联，因此要修正DOCTORUSER.DEPARTIDSQL 语句如下旧数据库类型A:DELETE FROM MedImgMgrSysLG.dbo.tbDepartment;DELETE FROM EndoscopeLG.dbo.DEPART WHERE DEPARTID=-1;UPDATE EndoscopeLG.dbo.DEPART SET DEPARTID=0 WHERE DEPARTID=31;UPDATE EndoscopeLG.dbo.DOCTORUSER SET DEPARTID=0 WHERE DEPARTID=31;INSERT MedImgMgrSysLG.dbo.tbDepartment(DepartmentID, DepartmentName) SELECT DEPARTID, DEPARTDESC FROM EndoscopeLG.dbo.DEPART;2. DOCTORUSER - tbDoctor(1)LEVELCODE-AuthCode255-10231-12-563(2)LOGINPWD-LoginPasswd(3)WORKID-WorkIDDOCTORUSER.WorkID = tbDoctor.WORKID + 1000?(4) HIRESTATUS-HireStatusDOCTORUSER.Status = tbDoctor.HIRESTATUS - 1(5) SEX-Sex0-11-22-0(6) DUTYID-DutyIDDOCTORUSER.DutyID = tbDoctor.DUTYID - 1(7) CNNAME- LoginName, NameCNDOCTORUSER.LoginName = tbDoctor.CNNAMEDOCTORUSER.NameCN = tbDoctor.CNNAME(8) DEPARTID-DepartmentIDDOCTORUSER.DepartmentID = tbDoctor.DEPARTIDSQL 语句如下旧数据库类型A:首先修改LEVELCODE类型由tinyint改为int，然后执行UPDATE EndoscopeLG.dbo.DOCTORUSER SET LEVELCODE=1023 WHERE LEVELCODE=255;UPDATE EndoscopeLG.dbo.DOCTORUSER SET LEVELCODE=563 WHERE LEVELCODE=2;UPDATE EndoscopeLG.dbo.DOCTORUSER SET SEX=SEX+1;UPDATE EndoscopeLG.dbo.DOCTORUSER SET SEX=0 WHERE SEX=3;UPDATE EndoscopeLG.dbo.DOCTORUSER SET HIRESTATUS=HIRESTATUS;UPDATE EndoscopeLG.dbo.DOCTORUSER SET DUTYID=DUTYID-1;INSERT MedImgMgrSysLG.dbo.tbDoctor(WorkID, DepartmentID, LoginName, LoginPasswd, AuthCode, HireStatus, DutyID, Sex) SELECT WORKID, DEPARTID, CNNAME, ISNULL(LOGINPWD,), LEVELCODE, ISNULL(HIRESTATUS, 0), DUTYID, SEX FROM EndoscopeLG.dbo.DOCTORUSER;3. tbPatientInformation - PATIENTE, PATIENTSELF(1)MedicalID -PATIENTSELF.MEDICCAREID(2)InHospitalID-PATIENTSELF.INHOSPITAL(3)OutPatientID-IF (PATIENTSELF.INHOSTPITAL = NULL) IDPATIENTSELF.SICKBEDID ELSE NULL(4)CheckID-该表的CheckID 根据旧版数据库的版本情况，由两种可能 1 图片按PATIENTSELF.ENDSCPID存放的要对同一机台号的同一天的病例进行处理，生成RECORDCDNO然后同2处理 2 图片按PATIENTSELF.RECORDCDNO存放的CheckID-PATIENTSELF.RECORDCDNO(5)InstrumentID-PATIENTSELF.INSTRUMENTID(6)PatientName-PATIENTSELF.PATIENTNAME(7)Sex-PATIENTSELF.SEX0-11-22-0(8)Age-PATIENTSELF.AGEAgeType-PATIENTSELF.AGEIF PATIENTSELF.AGE 200 THENtbPatientInformation.Age = PATIENTSELF.AGEtbPatientInformation.AgeType = 0IF PATIENTSELF.AGE 200 AND PATIENTSELF.AGE 300 THENtbPatientInformation.Age = PATIENTSELF.AGE - 300tbPatientInformation.AgeType = 2(9)Address-PATIENTSELF.ADDRESS(10)Phone-PATIENTSELF.PHONE(11)AskDepartment-PATIENTSELF.ASKDEPT(12)AskDoctor-PATIENTSELF.ASKDR(13)CheckDoctor-PATIENTSELF.CHECKDR(14)CheckDepartment-NULL(15)CheckTime-PATIENTSELF.CHECKTIME(16)ReportDoctor-PATIENTSELF.REPORTDR(17)ReportDepartment-NULL(18)ReportTime-PATIENTSELF.REPORTTIME(19)ImageCount-PATIENTSELF.IMAGECOUNT(20)CheckStatus-PATIENTSELF.CHECKSTATUS(21)CheckDesc-PATIENTSELF.CHECKDESC(22)DiagDesc-PATIENTSELF.DIAGNOSEDESC(23)PatientType-0(24)Fee-PATIENTE.FEE(25)AskAdvice-PATIENTE.ASKADVICE(26)CheckPos-PATIENTE.CHECKPARTDESC(27)BedID-IF (PATIENTSELF.INHOSTPITAL != NULL) IDPATIENTSELF.SICKBEDID ELSE NULL(28)OrganCount-(30)Positive-对于有阳性的数据表特殊处理SQL 语句如下旧数据库类型A:(龙岗78M数据库)修改PATIENTSELF表，将PATIENTE中的部分字段FEE、ASKADVICE、CHECKPARTDESC加入其中修改PATIENTSELF表，加入AGETYPE字段，类型int修改PATIENTSELF表，加入OUTPAITENTID字段，类型varchar 20修改PATIENTSELF表，加入ASKDATE字段，类型varchar 20修改PATIENTSELF表，加入CHECKDAYID字段，类型int选中旧数据库UPDATE PATIENTSELF SET PATIENTSELF.FEE=PATIENTE.FEE, PATIENTSELF.ASKADVICE=PATIENTE.ASKADVICE, PATIENTSELF.CHECKPARTDESC=PATIENTE.CHECKPARTDESC FROM PATIENTSELF, PATIENTE WHERE PATIENTSELF.ENDSCPID=PATIENTE.ENDSCPID AND PATIENTSELF.INSTRUMENTID=PATIENTE.INSTRUMENTID;/清除错误的数据DELETE FROM PATIENTSELF WHERE ASKTIME IS NULL;UPDATE PATIENTSELF SET AGETYPE=0 WHERE AGE200 AND AGE300;UPDATE PATIENTSELF SET SEX=SEX+1;UPDATE PATIENTSELF SET SEX=0 WHERE SEX=3;UPDATE PATIENTSELF SET OUTPAITENTID=SICKBEDID, SICKBEDID=NULL WHERE INHOSPITAL IS NULL;UPDATE PATIENTSELF SET OUTPAITENTID=SICKBEDID, SICKBEDID= WHERE INHOSPITAL=;UPDATE PATIENTSELF SET ASKDATE=LTRIM(STR(DATEPART(year, ASKTIME)+RIGHT(CAST(POWER(10,2) AS varchar)+DATEPART(month, ASKTIME),2)+RIGHT(CAST(POWER(10,2) AS varchar)+DATEPART(day, ASKTIME),2);DROP PROCEDURE UPDATECHECKDAYID;CREATE PROCEDURE UPDATECHECKDAYID INSTRUID INTASDECLARE dayid intDECLARE patientid int-临时变量，用来保存游标值DECLARE askdate varchar(20)DECLARE oldaskdate varchar(20)set dayid=1set oldaskdate=BEGIN TRAN -申明事务-申明游标 为patientidDECLARE order_cursor CURSOR FOR SELECT PATIENTID, ASKDATE FROM PATIENTSELF WHERE INSTRUMENTID=INSTRUID ORDER BY ASKDATE-打开游标open order_cursorFETCH NEXT FROM order_cursor INTO patientid, askdateWHILE FETCH_STATUS = 0 -返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。begin if askdate=oldaskdate beginset dayid=dayid+1 end else beginset dayid=1set oldaskdate=askdate end -执行sql操作 UPDATE PATIENTSELF SET CHECKDAYID=dayid WHERE PATIENTID=patientid FETCH NEXT FROM order_cursor INTO patientid, askdateendCOMMIT TRAN-提交CLOSE order_cursor-关闭游标DEALLOCATE order_cursor-释放游标GO针对每一个机台号，执行EXEC UPDATECHECKDAYID INSTRUID = 1EXEC UPDATECHECKDAYID INSTRUID = 2EXEC UPDATECHECKDAYID INSTRUID = 3EXEC UPDATECHECKDAYID INSTRUID = 4EXEC UPDATECHECKDAYID INSTRUID = 5EXEC UPDATECHECKDAYID INSTRUID = 6EXEC UPDATECHECKDAYID INSTRUID = 7.UPDATE PATIENTSELF SET RECORDCDNO=ASKDATE+RIGHT(CAST(POWER(10,3) AS varchar)+CHECKDAYID,3);INSERT MedImgMgrSysLG.dbo.tbPatientInformation(MedicalID, InHospitalID, OutPatientID, CheckID, InstrumentID, PatientName, Sex, Age, AgeType, Address, Phone, AskDepartment, AskDoctor, CheckDoctor, CheckTime, ReportDoctor, ReportTime, ImageCount, CheckStatus, CheckDesc, DiagDesc, PatientType, Fee, AskAdvice, CheckPos, BedID) SELECT MEDICCAREID, INHOSPITAL, OUTPAITENTID, RECORDCDNO, I