保存一段从Baan ERP LN的Oracle数据库中导出数据到SQL Server的SQL语句,前提是在MSSQL 2005中建立Link Server。
<br/>/*************** tibom010 *********************/<br/><br/>Truncate Table LNtibom010<br/><br/>Insert into LNtibom010(mitm<br/> ,pono<br/> ,seqn<br/> ,sitm<br/> ,indt<br/> ,exdt<br/> ,qana<br/> ,cwar<br/> ,cpha<br/> ,scpf<br/> ,scpq)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$mitm,T$pono,T$seqn,T$sitm,T$indt,T$exdt,T$qana,T$cwar,T$cpha,T$scpf,T$scpq from Baan.Ttibom010801')<br/><br/>Update LNtibom010 Set mitm = rtrim(mitm),sitm = rtrim(sitm)<br/><br/>/*************** tcibd001 *********************/<br/><br/>Truncate Table LNtcibd001<br/><br/>Insert into LNtcibd001(item<br/> ,kitm<br/> ,citg<br/> ,dsca<br/> ,uset<br/> ,cuni<br/> ,cwun<br/> ,wght<br/> ,csig<br/> ,ctyp<br/> ,ctyo<br/> ,cpcl<br/> ,cean<br/> ,lmdt<br/> )<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$item,T$kitm,T$citg,T$dsca,T$uset,T$cuni,T$cwun,T$wght,T$csig,T$ctyp,T$ctyo,T$cpcl,T$cean,T$lmdt from Baan.Ttcibd001801')<br/><br/>Update LNtcibd001 Set item = rtrim(item),citg = rtrim(citg),dsca = rtrim(dsca)<br/><br/>/*************** whwmd400 *********************/<br/><br/>Truncate Table LNwhwmd400<br/><br/>Insert into LNwhwmd400(item,abcc,hght,wdth,dpth)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$item,T$abcc,T$hght,T$wdth,T$dpth from Baan.Twhwmd400801')<br/><br/>Update LNwhwmd400 Set item = rtrim(item),abcc = rtrim(abcc)<br/><br/>/*************** tdipu010 *********************/<br/><br/>Truncate Table LNtdipu010<br/><br/>Insert into LNtdipu010(item,otbp,efdt,exdt,cofc,pref,ibps,prio,srcp,qimf,qimi,qima,qifi,qiec,cfrw,suti)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$item,T$otbp,T$efdt,T$exdt,T$cofc,T$pref,T$ibps,T$prio,T$srcp,T$qimf,T$qimi,T$qima,T$qifi,T$qiec,T$cfrw,T$suti from Baan.Ttdipu010801')<br/><br/>Update LNtdipu010 Set item = rtrim(item)<br/><br/>/*************** tdipu010 *********************/<br/><br/>Truncate Table LNtdipu001<br/><br/>Insert into LNtdipu001(item,cuqp,cupp,ccur,prip,otbp,buyr,cofc,suti)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$item,T$cuqp,T$cupp,T$ccur,T$prip,T$otbp,T$buyr,T$cofc,T$suti from Baan.Ttdipu001801')<br/><br/>Update LNtdipu001 Set item = rtrim(item)<br/><br/>/*************** tcibd004 Item Code by Item Code system *********************/<br/><br/>Truncate Table LNtcibd004<br/><br/>Insert into LNtcibd004(citt,bpid,item,aitc)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$citt,T$bpid,T$item,T$aitc from Baan.Ttcibd004801')<br/><br/>Update LNtcibd004 Set item = rtrim(item),aitc = rtrim(aitc)<br/><br/>/*************** tcibd200 Item Ordering Data *********************/<br/><br/>Truncate Table LNtcibd200<br/><br/>Insert into LNtcibd200(item,opol,osys,crmp,omth,kctr,cwar,oqmf,mioq,maoq,fioq,ecoq,reop,oint,oivu,sftm,sfst,maxs,cplb,fodt,lodt)<br/>SELECT *<br/>FROM OPENQUERY([CHLNDB],'Select T$item,T$opol,T$osys,T$crmp,T$omth,T$kctr,T$cwar,T$oqmf,T$mioq,T$maoq,T$fioq,T$ecoq,T$reop,T$oint,T$oivu,T$sftm,T$sfst,T$maxs,T$cplb,T$fodt,T$lodt from Baan.Ttcibd200801')<br/><br/>Update LNtcibd200 Set item = rtrim(item)<br/>