《旧版数据导入新版数据方法.doc》由会员分享，可在线阅读，更多相关《旧版数据导入新版数据方法.doc（6页珍藏版）》请在金锄头文库上搜索。
1、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;UPDAT
2、E 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.Wo
3、rkID = 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-DepartmentI
4、DDOCTORUSER.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 Endoscope
5、LG.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, CNNAM
6、E, 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 EL
7、SE 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-PATIENTSEL
8、F.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-
9、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-PATIENT
10、SELF.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
11、 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
12、 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 A
13、SKTIME 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
