金蝶总账数据转为用友的方法

卖卖

金蝶总账数据转换为用友的操作方法
在此说明的金蝶总账数据转换为用友总账数据的方法是采用账套(数据库)之间的数据导入进行转换的，主要分为总账的期初数据和凭证数据的转换，具体操作方法如下：
一、        首先，在金蝶账套上建立视图：
create view t_csup as
SELECT dbo.t_ItemDetailV.FDetailID, dbo.t_ItemDetailV.FItemID,
       dbo.t_Supplier.FNumber AS ccup_id
FROM dbo.t_ItemDetailV INNER JOIN
     dbo.t_Supplier ON dbo.t_ItemDetailV.FItemID = dbo.t_Supplier.FItemID
go

create view t_cus as
SELECT dbo.t_ItemDetailV.FDetailID, dbo.t_ItemDetailV.FItemID,
       dbo.t_Organization.FNumber AS ccus_id
FROM dbo.t_ItemDetailV INNER JOIN
     dbo.t_Organization ON dbo.t_ItemDetailV.FItemID = dbo.t_Organization.FItemID
go

create view t_balancels as
SELECT dbo.t_Balance.FYear, dbo.t_Balance.FPeriod, dbo.t_Balance.FAccountID,
       dbo.t_Balance.FDetailID, dbo.t_Balance.FCurrencyID,
       dbo.t_Balance.FBeginBalanceFor, dbo.t_Balance.FDebitFor,
       dbo.t_Balance.FCreditFor,dbo.t_Balance.FYtdDebitFor, dbo.t_Balance.FYtdCreditFor,
       dbo.t_Balance.FEndBalanceFor, dbo.t_Balance.FBeginBalance,
       dbo.t_Balance.FDebit, dbo.t_Balance.FCredit, dbo.t_Balance.FYtdDebit,
       dbo.t_Balance.FYtdCredit, dbo.t_Balance.FEndBalance,
       dbo.t_Balance.FFrameWorkID, dbo.t_Account.FNumber, dbo.t_Account.FDC
FROM dbo.t_Account INNER JOIN
     dbo.t_Balance ON dbo.t_Account.FAccountID = dbo.t_Balance.FAccountID
WHERE (dbo.t_Balance.FCurrencyID = '1')
Go

create view t_banlance_cus as
SELECT dbo.t_Balance.FYear, dbo.t_Balance.FPeriod, dbo.t_Balance.FAccountID,
       dbo.t_Balance.FDetailID, dbo.t_Balance.FCurrencyID,
       dbo.t_Balance.FBeginBalanceFor, dbo.t_Balance.FDebitFor,
       dbo.t_Balance.FCreditFor,dbo.t_Balance.FYtdDebitFor, dbo.t_Balance.FYtdCreditFor,
       dbo.t_Balance.FEndBalanceFor, dbo.t_Balance.FBeginBalance,
       dbo.t_Balance.FDebit, dbo.t_Balance.FCredit, dbo.t_Balance.FYtdDebit,
       dbo.t_Balance.FYtdCredit, dbo.t_Balance.FEndBalance,
       dbo.t_Balance.FFrameWorkID, dbo.t_Account.FNumber, dbo.t_Account.FDC,
       dbo.t_cus.ccus_id
FROM dbo.t_Account INNER JOIN
     dbo.t_Balance ON dbo.t_Account.FAccountID = dbo.t_Balance.FAccountID INNER JOIN
dbo.t_cus ON dbo.t_Balance.FDetailID = dbo.t_cus.FDetailID
WHERE (dbo.t_Balance.FCurrencyID = '1')
Go

create view t_banlance_sup as
SELECT dbo.t_Balance.FYear, dbo.t_Balance.FPeriod, dbo.t_Balance.FAccountID,
       dbo.t_Balance.FDetailID, dbo.t_Balance.FCurrencyID,
-dbo.t_Balance.FBeginBalanceFor AS FBeginBalanceFor,
dbo.t_Balance.FDebitFor,
       dbo.t_Balance.FCreditFor, dbo.t_Balance.FYtdDebitFor,
dbo.t_Balance.FYtdCreditFor,
       dbo.t_Balance.FEndBalanceFor,
-dbo.t_Balance.FBeginBalance AS FBeginBalance,
       dbo.t_Balance.FDebit, dbo.t_Balance.FCredit, dbo.t_Balance.FYtdDebit,
       dbo.t_Balance.FYtdCredit, dbo.t_Balance.FEndBalance,
       dbo.t_Balance.FFrameWorkID, dbo.t_Account.FNumber, dbo.t_Account.FDC,
       dbo.t_csup.ccup_id AS csup_id
FROM dbo.t_Account INNER JOIN
     dbo.t_Balance ON dbo.t_Account.FAccountID = dbo.t_Balance.FAccountID INNER JOIN
     dbo.t_csup ON dbo.t_Balance.FDetailID = dbo.t_csup.FDetailID
WHERE (dbo.t_Balance.FCurrencyID = '1')
Go

create view t_voucherls as
SELECT dbo.t_Voucher.FVoucherID, dbo.t_Voucher.FDate, dbo.t_Voucher.FYear,
       dbo.t_Voucher.FPeriod, dbo.t_Voucher.FNumber, dbo.t_Voucher.FAttachments,
       dbo.t_Voucher.FPreparerID, dbo.t_Voucher.FCheckerID,
       dbo.t_VoucherEntry.FAccountID, dbo.t_VoucherEntry.FDC,
       dbo.t_VoucherEntry.FAmountFor, dbo.t_VoucherEntry.FAmount,
       dbo.t_VoucherEntry.FQuantity, dbo.t_VoucherEntry.FAccountID2,
       dbo.t_VoucherEntry.FEntryID, dbo.t_VoucherEntry.FExplanation,
       dbo.t_Account.FNumber AS code, dbo.t_User.FName AS chechkname,
       dbo.t_VoucherEntry.FDetailID
FROM dbo.t_Voucher INNER JOIN
     dbo.t_VoucherEntry ON
     dbo.t_Voucher.FVoucherID = dbo.t_VoucherEntry.FVoucherID INNER JOIN
     dbo.t_Account ON
     dbo.t_VoucherEntry.FAccountID = dbo.t_Account.FAccountID INNER JOIN
     dbo.t_User ON dbo.t_Voucher.FCheckerID = dbo.t_User.FUserID
go

create view t_voucherlsls as
SELECT dbo.t_voucherls.FVoucherID, dbo.t_voucherls.FDate,
dbo.t_voucherls.FYear,
       dbo.t_voucherls.FPeriod, dbo.t_voucherls.FNumber,
dbo.t_voucherls.FAttachments,
       dbo.t_voucherls.FAccountID, dbo.t_voucherls.FDC,
dbo.t_voucherls.FAmountFor,
       dbo.t_voucherls.FAmount, dbo.t_voucherls.FQuantity,
dbo.t_voucherls.FAccountID2,
       dbo.t_voucherls.FEntryID, dbo.t_voucherls.FExplanation,
dbo.t_voucherls.code,
       dbo.t_User.FName AS cmaker, dbo.t_User.FUserID,
dbo.t_voucherls.chechkname,
       dbo.t_voucherls.FDetailID
FROM dbo.t_voucherls INNER JOIN
     dbo.t_User ON dbo.t_voucherls.FPreparerID = dbo.t_User.FUserID
go

create view  t_voucheraihp as
SELECT dbo.t_voucherlsls.FVoucherID, dbo.t_voucherlsls.FDate,
dbo.t_voucherlsls.FYear,
       dbo.t_voucherlsls.FPeriod, dbo.t_voucherlsls.FNumber,
dbo.t_voucherlsls.FEntryID,
       dbo.t_voucherlsls.code, dbo.t_voucherlsls.FAmount, dbo.t_voucherlsls.FDC,
       dbo.t_voucherlsls.FAccountID, dbo.t_voucherlsls.FAccountID2,
       dbo.t_voucherlsls.FAttachments, dbo.t_voucherlsls.FQuantity,
       dbo.t_voucherlsls.FExplanation, dbo.t_voucherlsls.cmaker,
       dbo.t_voucherlsls.FUserID, dbo.t_voucherlsls.chechkname,
       dbo.t_Account.FNumber AS dfcode, dbo.t_voucherlsls.FDetailID
FROM dbo.t_voucherlsls INNER JOIN
     dbo.t_Account ON dbo.t_voucherlsls.FAccountID2 = dbo.t_Account.FAccountID
go

create view t_voucheraihpcus as
SELECT dbo.t_voucheraihp.FVoucherID, dbo.t_voucheraihp.FDate,
       dbo.t_voucheraihp.FYear, dbo.t_voucheraihp.FPeriod,
dbo.t_voucheraihp.FNumber,
       dbo.t_voucheraihp.FEntryID, dbo.t_voucheraihp.code,
dbo.t_voucheraihp.FAmount,
       dbo.t_voucheraihp.FDC, dbo.t_voucheraihp.FAccountID,
       dbo.t_voucheraihp.FAccountID2, dbo.t_voucheraihp.FAttachments,
       dbo.t_voucheraihp.FQuantity, dbo.t_voucheraihp.FExplanation,
       dbo.t_voucheraihp.cmaker, dbo.t_voucheraihp.FUserID,
       dbo.t_voucheraihp.chechkname, dbo.t_voucheraihp.dfcode,
       dbo.t_voucheraihp.FDetailID, dbo.t_cus.ccus_id
FROM dbo.t_voucheraihp INNER JOIN
     dbo.t_cus ON dbo.t_voucheraihp.FDetailID = dbo.t_cus.FDetailID   
go

create view  t_voucheraihpsup as
SELECT dbo.t_voucheraihp.FVoucherID, dbo.t_voucheraihp.FDate,
       dbo.t_voucheraihp.FYear, dbo.t_voucheraihp.FPeriod,
dbo.t_voucheraihp.FNumber,
       dbo.t_voucheraihp.FEntryID, dbo.t_voucheraihp.code,
dbo.t_voucheraihp.FAmount,
       dbo.t_voucheraihp.FDC, dbo.t_voucheraihp.FAccountID,
       dbo.t_voucheraihp.FAccountID2, dbo.t_voucheraihp.FAttachments,
       dbo.t_voucheraihp.FQuantity, dbo.t_voucheraihp.FExplanation,
       dbo.t_voucheraihp.cmaker, dbo.t_voucheraihp.FUserID,
       dbo.t_voucheraihp.chechkname, dbo.t_voucheraihp.dfcode,
       dbo.t_voucheraihp.FDetailID, dbo.t_csup.ccup_id
FROM dbo.t_voucheraihp INNER JOIN
     dbo.t_csup ON dbo.t_voucheraihp.FDetailID = dbo.t_csup.FDetailID
go

海讯科技

--楼主辛苦了，我在实际操作中发现以下问题：
--1、用友的科目在数据库中存在时没有‘.’分隔符，因此用楼主的方法导入的科目和凭证都需要把科目中间的点分隔符去掉，我使用了以下语句，我的用友科目级次为4-2-2-2-2

update code set ccode=  --更新科目表
        case when len(ccode)=7 then (substring(ccode,1,4)+substring(ccode,6,2))
             when len(ccode)=10 then (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2))
             when len(ccode)=13 then (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2)+substring(ccode,12,2))
             when len(ccode)=14 then  (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2)+substring(ccode,13,2))
             else ccode
        end  

update gl_accvouch set ccode= --更新凭证表
        case when len(ccode)=7 then (substring(ccode,1,4)+substring(ccode,6,2))
             when len(ccode)=10 then (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2))
             when len(ccode)=13 then (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2)+substring(ccode,12,2))
             when len(ccode)=14 then  (substring(ccode,1,4)+substring(ccode,6,2)+substring(ccode,9,2)+substring(ccode,13,2))
             else ccode
        end

--2、楼主导入的期初余额仅写入了gl_accsum表，用友的期初余额还要写gl_accvouch表，所以导入后在用友软件的期初余额中查不到数据，此问题我直接让客户重录期初数解决。

lys2016

还是“老一批”人技术过硬些啊 。

pdasong

工具在哪里？我需要工具

卖卖

二、        转换XXXX年的期初数据：
        转换（导入）会计科目
insert into ufdata_101_2003.dbo.code
(cclass,cclass_engl,cbook_type,cbook_type_engl,ccode,ccode_name,igrade,bend,
BPROPERTY,bcus,bsup)
select case when fnumber like '1%' THEN '资产'
when fnumber like '2%' THEN '负债'
when fnumber like '3%' THEN '权益'
when fnumber like '4%' THEN '成本' ELSE '损益' END,
case when fnumber like '1%' THEN 'ZC'
when fnumber like '2%' THEN 'FZ'
when fnumber like '3%' THEN 'QY'
when fnumber like '4%' THEN 'CB' ELSE 'SY' END,'金额式','JES',
fnumber,fname,flevel,fdetail,
case when FDC=1 then fdc else 0 end,
case when fdetailid=2 then 1 else 0 end,
case when fdetailid=1 then 1 else 0 end
from t_account order by fnumber

        转换（导入）客户档案
insert into ufdata_101_2003.dbo.Customer(ccuscode,ccusname,ccusabbname,ccccode)
select fnumber,fname,fname, '00' from t_Organization

        转换（导入）供应商档案
insert into ufdata_101_2003.dbo.vendor(cvencode,cvenname,cvenabbname,cvccode)
select fnumber,fname,fname, '00' from t_Supplier

        转换（导入）科目余额
insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,fperiod,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,2,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,3,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,4,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,5,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,6,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,7,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,8,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,9,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,10,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,11,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accsum
(ccode,cexch_name,iperiod,cbegind_c,cbegind_c_engl,mb,cendd_c,cendd_c_engl,me)
select fnumber,Null,12,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,
case when fdc=1 then fbeginbalance else -fbeginbalance end
from t_balancels
where fyear=2003 and fdetailid=0 AND (FPeriod = 1)

如风

虽然没有附加说明，但是还是不错的

卖卖

        转换（导入）客户辅助余额
insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,fperiod,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,2,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,3,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,4,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,5,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,6,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,7,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,8,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,9,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,10,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,11,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,ccus_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,12,ccus_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_cus
where fyear=2003  AND (FPeriod = 1)

        转换（导入）供应商辅助余额(类似客户辅助余额)
insert into ufdata_101_2003.dbo.gl_accass
(ccode,cexch_name,iperiod,csup_id,cbegind_c,cbegind_c_engl,mb,cendd_c,
cendd_c_engl,me)
select fnumber,Null,fperiod,csup_id,case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance,
case when fdc=1 then '借'else '贷' end,
case when fdc=1 then 'Dr'else 'Cr' end,fbeginbalance
from t_banlance_sup
where fyear=2003  AND (FPeriod = 1)

        转换（导入）客户辅助明细
insert into ufdata_101_2003.dbo.gl_accvouch
(iperiod,dbill_date,cbook,cdigest,ccode,cexch_name,md,ccus_id,idoc,inid,ibook)
select 0,'2002-12-30','demo','期初数据',fnumber,NULL,FBeginBalance,ccus_id,
1,1,1
from t_banlance_cus
where fyear=2003 and fperiod=1

        转换（导入）供应商辅助明细
insert into ufdata_101_2003.dbo.gl_accvouch
(iperiod,dbill_date,cbook,cdigest,ccode,cexch_name,mc,csup_id,idoc,inid,ibook)
select 0,'2002-12-30','demo','期初数据',fnumber,NULL,FBeginBalance,csup_id,
1,1,1
from t_banlance_sup
where fyear=2003 and fperiod=1

三、        转换XXXX年的凭证数据：
insert into ufdata_101_2003.dbo.gl_accvouch
(dbill_date,iperiod,csign,isignseq,ino_id,inid,ccode,md,mc,idoc,cbill,ccheck,
cdigest,ccus_id,csup_id,ccode_equal)
select fdate,fperiod,'记','1',fnumber,fentryid+1,code,
case when fdc=1 then famount else 0 end,
case when fdc=0 then famount else 0 end,
fattachments,cmaker,chechkname,fexplanation,null,null,dfcode
from t_voucheraihp
where fdetailid=0 and fyear=2003

insert into ufdata_101_2003.dbo.gl_accvouch
(dbill_date,iperiod,csign,isignseq,ino_id,inid,ccode,md,mc,idoc,cbill,ccheck,
cdigest,ccus_id,csup_id,ccode_equal)
select fdate,fperiod,'记','1',fnumber,fentryid+1,code,
case when fdc=1 then famount else 0 end,
case when fdc=0 then famount else 0 end,
fattachments,cmaker,chechkname,fexplanation,ccus_id,null,dfcode
from t_voucheraihpcus
where fyear=2003

insert into ufdata_101_2003.dbo.gl_accvouch
(dbill_date,iperiod,csign,isignseq,ino_id,inid,ccode,md,mc,idoc,cbill,ccheck,
cdigest,ccus_id,csup_id,ccode_equal)
select fdate,fperiod,'记','1',fnumber,fentryid+1,code,
case when fdc=1 then famount else 0 end,
case when fdc=0 then famount else 0 end,
fattachments,cmaker,chechkname,fexplanation,null,ccup_id,dfcode
from t_voucheraihpsup
where fyear=2003

特殊说明：用友软件按年度划分年度账套，但金蝶软件没有年度账套的概念，即依此类推如果需要继续将金蝶2004年的凭证数据转换为用友的凭证数据，则转换语句应为：
insert into ufdata_101_2004.dbo.gl_accvouch
(dbill_date,iperiod,csign,isignseq,ino_id,inid,ccode,md,mc,idoc,cbill,ccheck,
cdigest,ccus_id,csup_id,ccode_equal)
select fdate,fperiod,'记','1',fnumber,fentryid+1,code,
case when fdc=1 then famount else 0 end,
case when fdc=0 then famount else 0 end,
fattachments,cmaker,chechkname,fexplanation,null,null,dfcode
from t_voucheraihp
where fdetailid=0 and fyear=2004

……


四、        有关数据转换方法的其他辅助说明：
        查询金蝶XXXX年的年末科目余额：
SELECT * FROM t_balancels
WHERE (FYear = 2004) AND (FPeriod = 12)
ORDER BY FNumber
        查询金蝶软件的库表字段与库表结构：
字段：t_FieldDescription
表名：t_TableDescription

卖卖

注意修改数据库名称（ufdata_101_2003）  和会计年度（2003）

titan

这么个转法数据库会不会崩溃啊
毕竟2个系统的数据结构和表结构都不一样

dlkj323

这样整行得通不

cqchenyan

其实不用这么搞,有工具可以转数据的

陈秋月

其实不用这么麻烦的吧！这么做还不知道行不行得通，加一个数据接口，想咋搞就咋搞！

minx

原帖由 陈秋月 于 2008-6-29 21:53 发表

                               
登录/注册后可看大图

其实不用这么麻烦的吧！这么做还不知道行不行得通，加一个数据接口，想咋搞就咋搞！

哈哈,美女说的是啊,搞这么复杂做什么啊!

qisuo3

原帖由 minx 于 2008-6-29 22:00 发表

                               
登录/注册后可看大图

哈哈,美女说的是啊,搞这么复杂做什么啊!

怎么加数据接口啊?

LANGJI-TIANYA02

呵呵
接口是简单
但是人家这样也是技术啊

acong

很羡慕写语句的

不过还是希望有傻瓜化的工具

qiuyufeng

这，这也太复杂了吧

leweisoft

又要打工挣钱了，谢谢，好用的顶一下。