第二版系统数据库数据转支队系统数据库1

第二版系统数据库数据转支队系统数据库编著：赵仁培日期：2016-09-221.基本信息导入........................................................................................................................21.导单位信息....................................................................................................................22.没有表内自定义id编号的单位：...............................................................................33.需要注意表内自定义id编号的表：.........................................................................3建筑表/消控室表/联网设备表/视频设备表..................................................................34.xfssjc_city数据库执行步骤一，二:............................................................................4总结：...............................................................................................................................62.网站操作................................................................................................................................63.点位信息导入........................................................................................................................71.查询点位信息................................................................................................................7第一种：该单位点位信息完整，可直接导入。...........................................................7第二种：该单位点位信息=1，即测试单位...................................................................84.后期维护每日必须执行一遍，月末月初勤看..............................................................11说明服务器数据库简称192.169.3.2XFSSJC_CITY_BAKCity_bak192.168.1.60WyxfWyxf192.168.1.101XFSSJC_CITYcity1.基本信息导入1.导单位信息1.在city_BAK数据库查询单位信息，并导出2.在CITY数据库查询单位最大单位ID3.更改sql文件中的待导表名称，value数据中第二列数据即为单位ID，根据第二步获得的单位最大ID加1；即为DWID=”3206U00467”2.没有表内自定义id编号的单位：单位关联表/主机信息表/单位人员信息表1.在city_bak查询1214单位的dwjg_dwgx/jcxx_zj/jcxx_xfaqglry数据，并导出单位关联：select*fromdwjg_dwgxwherelwdwidin('1214000001')orderbylwdwid;单位人员：select*fromJCXX_XFAQGLRYwheredwidin('1214000001');主机信息：select*fromJCXX_ZJwhereLWDWIDin('1214000001');2.打开sql文件，更改待导表表名，values后lwdwid即为单位Id，替换成”3206U00467”3.导入CITY数据库dwjg_dwgx/jcxx_zj/jcxx_xfaqglry表3.需要注意表内自定义id编号的表：建筑表/消控室表/联网设备表/视频设备表1.在city_bak查询1214单位的jcxx_jzxx/xks_xfkzs/jcxx_lwsb_jcxx_spsb数据，并导出建筑信息：select*fromJCXX_JZXXwhereGLDWIDin('1214000001');消控室：select*fromXKS_XFKZSwhereDWIDin('1214000001');联网设备：select*fromJCXX_LWSBwhereLWDWIDin('1214000001');视频设备：select*fromJCXX_SPSBwhereshdwbmin('1214000001');2.在CITY数据库查询建筑编号最大id+1，消控室编号最大id+1，联网设备编号最大id+1，视频设备最大id+1selecttop30BUILDCODEfromJCXX_JZXXorderbyBUILDCODEdesc;selecttop20ZCBHfromXKS_XFKZSorderbyZCBHdesc;selecttop20LWSBBH,txdzmfromJCXX_LWSBorderbyLWSBBHdesc;selecttop50SPSBBHfromJCXX_SPSBorderbySPSBBHdesc;3.打开sql文件，更改待导表表名，values后lwdwid即为单位Id，替换成”3206U00467”，看图更详细4.在CITY数据库正式执行sql语句前，再执行一遍步骤2，以免有人同时执行相同操作，id冲突4.xfssjc_city数据库执行步骤一，二:步骤一：更新部分表新的id编号--更新新产生的编号updateJCXX_XFAQGLRYsetXKSBH=(selectZCBHfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_XFAQGLRY.DWID)whereRYLX='04'andDWIDin('3206U00467');updateJCXX_JZXXsetXFKZSBH=(selectZCBHfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_JZXX.GLDWID)whereGLDWIDin('3206U00467');updateXKS_XFKZSSETJZID=(selecttop1JCXX_JZXX.BUILDCODEfromJCXX_JZXXwhereJCXX_JZXX.GLDWID=XKS_XFKZS.DWID)whereDWIDin('3206U00467');updateJCXX_LWSBsetXKSBH=(selectZCBHfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_LWSB.LWDWID),SZJZBH=(selectJZIDfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_LWSB.LWDWID)whereLWDWIDin('3206U00467');updateJCXX_ZJsetXKSBH=(selectZCBHfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_ZJ.LWDWID),LWSBBH=(selectLWSBBHfromJCXX_LWSBwhereJCXX_LWSB.LWDWID=JCXX_ZJ.LWDWID),SZJZBH=(selectJZIDfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_ZJ.LWDWID)whereLWDWIDin('3206U00467');UPDATEJCXX_SPSBsetXKSBH=(selectZCBHfromXKS_XFKZSwhereXKS_XFKZS.DWID=JCXX_SPSB.SHDWBM)whereSHDWBMin('3206U00467');步骤二：更新录入时间，不同人请改动acc,accname,chgacc,chaaccnameupdateDWJG_LWDWsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGACCNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereDWIDin('3206U00467');updateJCXX_JZXXsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGACCNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereGLDWIDin('3206U00467');updateXKS_XFKZSsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereDWIDin('3206U00467');updateJCXX_LWSBsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGACCNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereLWDWIDin('3206U00467');updateJCXX_SPSBsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGACCNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereSHDWBMin('3206U00467');updateJCXX_ZJsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGACCNAME='万友消防赵仁培',CHGTIME=GETDATE(),CHGIP='10.170.73.169'whereLWDWIDin('3206U00467');updateJCXX_XFAQGLRYsetACC='J0000089',ACCNAME='万友消防赵仁培',INTIME=GETDATE(),INIP='10.170.73.169',CHGACC='J0000089',CHGNAME='万