很多時候,我們需要封存目前的SBO帳套、啟用新的SBO帳套。SBO系統對象都具有很好的資料傳遞性,而自定義業務對象就麻煩了,即使設定其為可以Transfer,自定義的業務對象仍然無法在新的帳套中産生連貫的序列,甚至連序列都沒法建立。
以下這幾段Sql語句就是為了應該這種情況的,它有效的解決了上述的問題。至于為什麼這個做,我相信經驗豐富的SBO工程師們看看就很清楚了。
恩,是的,在此的OldDb就是Sql中老的Sbo帳套對應的資料庫,而NewDb就是新帳套對應的資料庫了。
__insert into [NewDb]..onnm (ObjectCode,Autokey,DfltSeries)
__select ObjectCode,Autokey,DfltSeries + maxSeries - minSeries + 1 newSeries
from [OldDb]..onnm a,
(__select max(DfltSeries) maxSeries from [NewDb]..onnm) b,
(__select min(DfltSeries) minSeries from [OldDb]..onnm where ObjectCode not in (__select objectcode from [NewDb]..onnm)) c
where objectcode not in (__select ObjectCode from [NewDb]..onnm)
go
__insert into [NewDb]..nnm1(
ObjectCode, Series, SeriesName, InitialNum, NextNumber, LastNum, BeginStr,
EndStr, Remark, GroupCode, Locked, YearTransf, [Indicator], Template, NumSize,
FolioPref, NextFolio,DocSubType
)
__select a.ObjectCode, b.DfltSeries, a.SeriesName, a.InitialNum, a.NextNumber, a.LastNum, a.BeginStr,
a.EndStr, a.Remark, a.GroupCode, a.Locked, a.YearTransf, a.[Indicator], a.Template, a.NumSize,
a.FolioPref, a.NextFolio, a.DocSubType
FROM [OldDb]..NNM1 a inner join [NewDb]..onnm b . a.ObjectCode=b.ObjectCode
WHERE a.ObjectCode not in (__select ObjectCode FROM [NewDb]..nnm1)
__insert into [NewDb]..nnm2(ObjectCode, Series, DocSubType,UserSign)
__select a.ObjectCode, b.DfltSeries, a.DocSubType,1
FROM [OldDb]..NNM2 a inner join [NewDb]..onnm b . a.ObjectCode=b.ObjectCode
WHERE a.ObjectCode not in (__select ObjectCode FROM [NewDb]..nnm2)
__insert into [NewDb]..nnm3(ObjectCode, Series, DocSubType)
__select a.ObjectCode, b.DfltSeries, a.DocSubType
FROM [OldDb]..NNM3 a inner join [NewDb]..onnm b . a.ObjectCode=b.ObjectCode
WHERE a.ObjectCode not in (__select ObjectCode FROM [NewDb]..nnm3)
go
在SBO 2005B和Sql Server 2000中成功實施通過。
本文轉自foresun 51CTO部落格,原文連結:http://blog.51cto.com/foresun/128792,如需轉載請自行聯系原作者