简单例子
update TB2
set TB2.DIC=TB1.DIC,TB2.NAME=TB1.NAME from TB1
where TB2.ID=TB1.ID
下面是远处查询更新同步工作
/**将资产远程数据导入到
物料表**/
insert into WOO_IF_WZKC_TEMP(
ITEMNUM,
DESCRIPTION,
ORDERUNIT,
CUSTTZL1,
CUSTTZL2,
CUSTTZL3,
CUSTBZ,
CUSTJLXZ,
CUSTZHXS,
CUSTZHDW,
CUSTOLDITEM
,CUSTDEBZ
)
select ITEMNUM,
DESCRIPTION,
ORDERUNIT,
CUSTTZL1,
CUSTTZL2,
CUSTTZL3,
CUSTBZ,
CUSTJLXZ,
CUSTZHXS,
CUSTZHDW,
CUSTOLDITEM,
CUSTDEBZ
from openquery(ASSET_TEST, 'SELECT * FROM MAXIMO.IF_CUSTITEM') item where
not Exists(select ITEMNUM from WOO_IF_WZKC_TEMP t where t.ITEMNUM=item.ITEMNUM );
/**
*工单表(通过)
***/
insert into WOO_ZC_GD_TEMP(
CUSTGDBH
,DESCRIPTION
,CUSTGCGS
,CUSTLRR
,UNIQUEID
,CUSTSRRQ
,CUSTGCBH
,XMMC
,CUSTDZDH
,CUSTSGDW
--,CUSTDGDW
,CUSTDW
,CUSTGDLX
,LOCATION
,VALUE
,CUSTGCLX
,CUSTJGHCWSP
,WORKORDERID
)
select
CUSTGDBH
,DESCRIPTION
,CUSTGCGS
,CUSTLRR
,UNIQUEID
,CUSTSRRQ
,CUSTGCBH
,XMMC
,CUSTDZDH
,CUSTSGDW
--,CUSTDGDW
,CUSTDW
,CUSTGDLX
,LOCATION
,VALUE
,CUSTGCLX
,CUSTJGHCWSP
,WORKORDERID
from openquery(ASSET_TEST, 'SELECT * FROM MAXIMO.IF_CUSTGDGL') gd
where not Exists(select WORKORDERID from WOO_ZC_GD_TEMP t where t.WORKORDERID=gd.WORKORDERID)
/**
*采购计划
**/
insert into WOO_IF_CUSTCGJH_TEMP(
ITEMNUM
,CUSTSL
,CUSTLRR
,CUSTJE
,CUSTDGDW
-- ,CUSTHKDJ
,CUSTCGJHH
,CUSTXQJHBM
,XH
,CGY
,VALUE
,UNIQUEID
,UNITCOST
,CUSTJHRQ
,CUSTXMMC
,CUSTXMBM
,CUSTGWCGBZ
,MS
,GGXH
,PRLINEID
)
select
ITEMNUM
,CUSTSL
,CUSTLRR
,CUSTJE
,CUSTDGDW
-- ,CUSTHKDJ
,CUSTCGJHH
,CUSTXQJHBM
,XH
,CGY
,VALUE
,UNIQUEID
,UNITCOST
,CUSTXYRQ
,XMMC
,CUSTXMBM
,CUSTGWCGBZ
,MS
,GGXH
,PRLINEID
from
openquery(ASSET_TEST, 'SELECT * FROM MAXIMO.IF_CUSTCGJH') rkd
where not Exists(select PRLINEID from WOO_IF_CUSTCGJH_TEMP t where t.PRLINEID=rkd.PRLINEID)
/**
*入库单
**/
insert into WOO_IF_CUSTRKD_TEMP(
CUSTRKDBM
,CUSTRKDMS
,CUSTHTBM
,CUSTHKJE
,CUSTSE
,CUSTYZF
,CUSTYZFSE
,CUSTZF
,CUSTZJE
,CUSTZDR
,CUSTZDSJ
,CUSTCK
,CUSTCKFL
)
select CUSTRKDBM,CUSTRKDMS,CUSTHTBM,CUSTHKJE,CUSTSE,CUSTYZF,
CUSTYZFSE,CUSTZF,CUSTZJE,CUSTZDR,CUSTZDSJ,CUSTCK,CUSTCKFL from
openquery(ASSET_TEST, 'SELECT * FROM MAXIMO.IF_CUSTRKD') rkd
where not Exists(select CUSTRKDBM from WOO_IF_CUSTRKD_TEMP t where t.CUSTRKDBM=rkd.CUSTRKDBM)
--工单的更新---------------------------------------------------------------------------------
/**
*更新工单表(通过)
***/
update WOO_ZC_GD_TEMP
set
CUSTGDBH=zcgd.CUSTGDBH
,DESCRIPTION=zcgd.DESCRIPTION
,CUSTGCGS=zcgd.CUSTGCGS
,CUSTLRR=zcgd.CUSTLRR
,UNIQUEID=zcgd.UNIQUEID
,CUSTSRRQ=zcgd.CUSTSRRQ
,CUSTGCBH=zcgd.CUSTGCBH
,XMMC=zcgd.XMMC
,CUSTDZDH=zcgd.CUSTDZDH
,CUSTSGDW=zcgd.CUSTSGDW
--,CUSTDGDW
,CUSTDW=zcgd.CUSTDW
,CUSTGDLX=zcgd.CUSTGDLX
,LOCATION=zcgd.LOCATION
,VALUE=zcgd.VALUE
,CUSTGCLX=zcgd.CUSTGCLX
,CUSTJGHCWSP=zcgd.CUSTJGHCWSP
from (
select
CUSTGDBH
,DESCRIPTION
,CUSTGCGS
,CUSTLRR
,UNIQUEID
,CUSTSRRQ
,CUSTGCBH
,XMMC
,CUSTDZDH
,CUSTSGDW
--,CUSTDGDW
,CUSTDW
,CUSTGDLX
,LOCATION
,VALUE
,CUSTGCLX
,CUSTJGHCWSP
,WORKORDERID
from openquery(ASSET_TEST, 'SELECT * FROM MAXIMO.IF_CUSTGDGL') gd
where Exists(select WORKORDERID from WOO_ZC_GD_TEMP t where t.WORKORDERID=gd.WORKORDERID)) as zcgd
where WOO_ZC_GD_TEMP.WORKORDERID=zcgd.WORKORDERID
