网站的前端和后台,wordpress批量插件,如何做企业文化培训,网站项目进度:::info #x1f4a1; 整体业务流程 从A9服务器中取数#xff0c;生成列表数据#xff0c;写入到对方oracle数据库中。 :::
项目关键点
1.连接数据库 左连接连接本地SQLserver数据库、右连接要链接A9开票服务器的数据库然后设想用SQLserver 自带的外部连接来连接oracle数据…:::info 整体业务流程 从A9服务器中取数生成列表数据写入到对方oracle数据库中。 :::
项目关键点
1.连接数据库 左连接连接本地SQLserver数据库、右连接要链接A9开票服务器的数据库然后设想用SQLserver 自带的外部连接来连接oracle数据库。 add keyRConnectionString valueProviderPostgreSQL OLE DB Provider;PasswordAiSinO_618;User IDfwkp_320901999999116_0;Data Source192.168.2.153;Locationfwkp_320901999999116_0 /用外部连接连接oracle数据库
2.用语句验证取数
获取A9开票服务器数据
SELECT
:sClientNo as imTaxMachineNo,
d.fpmxxh AS idIndex,
m.fpdm || - || m.fphm AS imID,
m.fpdm || - || m.fphm AS imsaleIDlist,
s AS imType,
m.gfmc AS imCrpName, AS imCrpTaxNo, AS imCrpAddrTel, AS imCrpBankNo,
to_char(m.kprq,YYYY-MM-DD) as imdate,
m.bz AS imremark, AS imOper, AS imChecker, AS imPayee,
AS imTaxrate,
d.spmc as idgoodsname,
d.ggxh AS idModal,
d.jldw AS idUnit,
d.sl AS idCount,
d.dj AS idPrice,
cast(d.je as VARCHAR) AS idMoney,
cast(d.se as VARCHAR) AS idTaxMoney,
0 AS imIncludeTaxFlg,
0 AS idMoneyIncludeTaxFlg,
0 AS idPriceIncludeTaxFlg,
d.flbm AS idGoodsNo,
cast (d.slv as numeric(20,2))*cast(100 as numeric(20,2)) AS idTaxRate,
m.gfmc as idDefine7,as idDefine8,
m.fphm AS imSaleNo,
m.fpdm || - || m.fphm AS imSaleID,
m.fpdm || - || m.fphm AS idID,
m.fpdm || - || m.fphm AS idimID ,
数据库 as imDefine10,
m.fpdm as imDefine9,
m.fphm as imDefine8,
m.kpjh as imDefine7,
m.zfbz as imStatus,
m.fpzl as imCurrType,
m.xfmc as imSaleName,
--(select top 1 spmc from xxfpTaxCardD dd where m.fpdmdd.fpdm and m.fphmdd.fphm) as imDefine10,--as imDefine4 --FROM skfp m , skfp_mx d
where m.fphmd.fphm
and m.kprq between to_date(:s起始日期,YYYY-MM-DD) and to_date(:s截止日期,YYYY-MM-DD)
and m.kpjh :s开票点验证外部连接插入oracle 数据库
insert
into
openquery(ORCL,select
COMPANY,PREPAREDDATE,PK_BILL,ATTACHMENT_NUMBER,INDEXID,ACCOUNT_CODE,ABSTRACT_SM,DIRECTION,AMOUNT,FREETYPE1,FREEVALUE1,FREETYPE2,FREEVALUE2,FREETYPE3,FREEVALUE3,FREETYPE4,FREEVALUE4,FREETYPE5,FREEVALUE5,NCFLAG,NCINFO,TS,FPTYPE
from
DF.TEMP_VOUCHER)
select
COMPANY,PREPAREDDATE,PK_BILL,ATTACHMENT_NUMBER,count,ACCOUNT_CODE,ABSTRACT_SM,DIRECTION,AMOUNT,FREETYPE1,FREEVALUE1,FREETYPE2,FREEVALUE2,FREETYPE3,FREEVALUE3,FREETYPE4,FREEVALUE4,FREETYPE5,FREEVALUE5,NCFLAG,NCINFO,TS,FPTYPE
from
TEMP_VOUCHER where bz成功3.注意点 防止插入重复数据 select distinct * into #Tmp from TEMP_VOUCHER
drop table TEMP_VOUCHER
select * into TEMP_VOUCHER from #Tmp
drop table #Tmpalter table TEMP_VOUCHER add idAutoID int identity (1,1)
DBCC CHECKIDENT (TEMP_VOUCHER,reseed,1)
update TEMP_VOUCHER set countt.RowNumber from (SELECTpk_billcast(idautoid as varchar(1000)) pkbill ,ROW_NUMBER() OVER (ORDER BY pk_billiddefine2iddefine3iddefine1cast(indexid as varchar(1000))) as RowNumber FROM TEMP_VOUCHER where bz)t where t.pkbillTEMP_VOUCHER.pk_billcast(TEMP_VOUCHER.idautoid as varchar(1000)) and TEMP_VOUCHER.bz
