金蝶專業(yè)版導(dǎo)庫語句

1、金蝶專業(yè)版導(dǎo)庫語句說明 :金蝶 KIS 迷你版或標(biāo)準(zhǔn)版升到金蝶KIS 專業(yè)版時 ,賬套數(shù)據(jù)升級時,由于數(shù)據(jù)結(jié)構(gòu)不一樣,經(jīng)常出現(xiàn)數(shù)據(jù)傳值出錯、無法結(jié)賬、報表運(yùn)行出錯等。這時可新建一賬套,將原來的賬套數(shù)據(jù)導(dǎo)入新賬套里.use AcctCtluse AcctCommerceselect * from t_ad_kdAccount_gluse AIS20100416155547-新賬套if Exists(select * from sysobjects wherename=N'sp_ExportDatabase' And Xtype='P')Drop PROCEDURE

2、 sp_ExportDatabaseGoCreate PROCEDURE sp_ExportDatabase(SourceDB varchar(100) )ASBeginSet NoCount OnDeclare Utb sysnameDeclare ColName sysnameDeclare tid intDeclare sql nvarchar(3000)Declare len int-定義游標(biāo)取回用戶建立的表sELECT SourceDB=SourceDB+'.'Declare Ctb Cursor ForSelect name,id FromsysobjectsWhe

3、re xtype ='U'- and name<>'Com_CostDataIn'(排除某些表)Order by name Open CtbFetch Ctb Into Utb,tid While (FETCH_STATUS=0) BeginSelect Utb='Dbo.'+UtbSelect SQL='Alter Table '+Utb+' Disable TriggerAll; '+ ' ALTER TABLE '+ Utb +' NOCHECKCONSTR

4、AINT All; 'exec ( SQL)Fetch Ctb Into Utb,tidEndclose ctbOpen CtbFetch Ctb Into Utb,tidWhile (FETCH_STATUS=0)BeginSelect Utb='Dbo.'+Utbexec (' Delete ' + Utb)Set sql=''Declare Clu Cursor For Select name From syscolumns Where id=tid And iscomputed=0 and xtype<&gt

5、;189Open CluFetch Clu Into ColNameWhile (FETCH_STATUS=0)BeginSet sql=sql+ ColName + ','Fetch Clu Into ColNameEndClose CluDeAllocate Clu-構(gòu)造字符串Set len=Len(sql)If len>0BeginSelect sql=left(sql,len-1)Set sql='Insert Into '+ Utb + ' ('+sql+') '+' Select'+sql

6、+' From ' + SourceDB+ Utb print 'Importing Table : ' +utb+'.'If Exists (Select name From syscolumns Where id=tid and status=0x80)Select SQl='Set IDENTITY_INSERT '+ Utb + ' ON' + ' Delete ' + Utb+' '+sqlElseSelect SQl=sqlExec ( sql)If Exists (Se

7、lect name From syscolumns Where id=tid and status=0x80)Exec( 'Set IDENTITY_INSERT '+ Utb + ' Off')print 'Importing Table : ' +utb+' complete'EndFetch Next From Ctb Into Utb,tidEndClose CtbOpen CtbFetch Ctb Into Utb,tidWhile (FETCH_STATUS=0)BeginSelect Utb='Dbo.'+Utbselect sql='Alter Table '+Utb+' Enable Trigger All '+ ' ALTER TABLE '+ Utb +' CHECK CONSTRAINT All 'Exec sp_executesql sqlFetch Ctb Into Utb,tidEndclose ctbDeAllocate Ctbprint 'Import database complete!'return