Import data from one database into another database (DB2)

Import data from one database into another database (DB2)

I take the example here is the use of the DB2 database, other database ideas are like this!

1. Import data from a table into local Excel from the DB2 database

 to  of  by Select *  from Iout_busi_ywdjmx_temp

2. Create a temporary table in the database you want to import

--Create a temporary tableCREATE TABLENbadv.l_hzcitywa (Fhcodevarchar( -), Dhcodevarchar( -), AwayDECIMAL( +,4) ) DATA CAPTURE NONEinch[email protected]ALTER TABLEnbadv.l_hzcitywalocksize rowappendOFF not[Email protected]

3. Import the Excel file in CSV format into a temporary table in the database

-- import data into a staging table  from  of  INSERT into [email protected]

4. Processing the data

--duplicate data is detectedSelect distinctA.deli_unit_cd,c.fhcode,a.arri_unit_cd,c.dhcode,Count(C.away) fromSt_i_std_mileage aInner JoinNbadv.l_hzcitywa C onA.deli_city_nm=C.fhcode andA.arri_city_nm=C.dhcodeGroup  byA.deli_unit_cd,c.fhcode,a.arri_unit_cd,c.dhcode having Count(*)>1--Delete duplicate dataDelete  fromNbadv.l_hzcitywawhereDhcodeinch('Beijing','Dongguan','Lhasa','Tianshui','Jiuquan','Pingliang','Qingyang','Tongchuan','Xianyang','Yantai')@

5, the processed data through the DB2 stored procedures to update the required data to the table of the second database, where the key is to find the temporary table and the need to update the data table in the association;

--create a stored procedure to update a contract mileageDrop procedure[email protected]Create procedureL_getcityway (inchV_personidint) Language sqldynamic result sets0beginDeclareSqlcodeint;DeclareSQLStateChar(5);DeclareV_errcodeint default 0;--Renew a contract mileageMerge intost_i_trans_mileage ausing (Select distinctA.deli_unit_cd,c.fhcode,a.arri_unit_cd,c.dhcode,c.away fromSt_i_std_mileage aInner JoinNbadv.l_hzcitywa C onA.deli_city_nm=C.fhcode andA.arri_city_nm=C.dhcode andC.fhcode= 'Weinan') b onA.deli_store_cd=B.deli_unit_cd andA.arri_store_cd=B.ARRI_UNIT_CD whenMatched Then Update SetA.actual_mileage=B.away;--Calculate VarianceUpdateSt_i_trans_mileageSet difference=(Actual_mileage-Std_mileage)*100.000000/Std_mileagewhereDeli_store_cd=2061000101  andStd_mileage is  not NULL  andStd_mileage<>0;--Update NotesUpdateSt_i_trans_mileageSetREMARK='No' where difference>Ten  andDeli_store_cdinch('2061000101','2061000102','2061000103','2061000104','2061000105');returnV_errcode;End@

In this case, the data from the table in the first database has been implemented to update to the second database;

Import data from one database into another database (DB2)