导数据模板

--更新已经有的数据
update Z_CustomerExtendedData
set CustomerEnglishName = CustomerExtended200520.NameEnglish
,SubSegmentCodeAP = CustomerExtended200520.CustomerClass7APCode
,SICCode = CustomerExtended200520.CustomerClass8GlobalCode
,BankName = CustomerExtended200520.BankReference1
,BankAccountNo = CustomerExtended200520.BankReference2
,PaymentTerm = CustomerExtended200520.PaymentNetDays
from CustomerExtended200520
where Z_CustomerExtendedData.CustomerID = CustomerExtended200520.CustomerNumber
and CustomerID in
(select CustomerID from Z_CustomerExtendedData
where CustomerID in
(
select CustomerNumber from CustomerExtended200520
))

--插入没有的数据
insert into Z_CustomerExtendedData
select 
[CustomerNumber], [NameEnglish],'',[BankReference1], [BankReference2], [CustomerClass7APCode]
, [CustomerClass8GlobalCode], null,null,null, null,null,null,[PaymentNetDays]
, null,null,null, null,null,null, null,null,null, null,null,null,null, null,null,null
from CustomerExtended200520
where CustomerNumber
in (
select CustomerNumber from CustomerExtended200520
where CustomerNumber not in
(
select CustomerID from Z_CustomerExtendedData
)
)

 

--更新

update Z_APCode set
[ExistinGlobal]=APCode200601.[ExistinGlobal],
[GlobalCode]=APCode200601.[GlobalCode],
[APCodeDesc]=APCode200601.[APCodeDesc],
[LongDesc]=APCode200601.[LongDesc],
[APStatus]=APCode200601.[APStatus],
[CommercialCode]=APCode200601.[CommercialCode],
[ProjectNumber]=APCode200601.[ProjectNumber],
[AlternateItem]=APCode200601.[AlternateItem],
[Drawing]=APCode200601.[Drawing],
[DrawingRev]=APCode200601.[DrawingRev],
[UM]=APCode200601.[UM],
[TechnologyOwner]=APCode200601.[TechnologyOwner],
[APMasterOwner]=APCode200601.[APMasterOwner],
[Remark]=APCode200601.[Remark],
[PERange]=APCode200601.[PERange],
[SOPFamily]=APCode200601.[SOPFamily],
[LeadTime]=APCode200601.[LeadTime],
[Series]=APCode200601.[Series],
[ProductBrand]=APCode200601.[ProductBrand],
[CatalogGroups]=APCode200601.[CatalogGroups], 
[Proprietary]=APCode200601.[Proprietary],
[IndustrySegment]=APCode200601.[IndustrySegment],
[ProductRang]=APCode200601.[ProductRange],
[ProgramID]=APCode200601.[ProgramID],
[PreferredSupplier]=APCode200601.[PreferredSupplier],
[HTSCode]=APCode200601.[HTSCode],
[DRICode]=APCode200601.[DRICode],
[ECCN]=APCode200601.[ECCN],
[UpdateDate] = GETDATE(),
[UpdateBy] = 'haojie.tang'
from APCode200601
where Z_APCode.APCode = APCode200601.APCode
and Z_APCode.APCode in
(select APCode from Z_APCode
where APCode in
(
select APCode from APCode200601
))