文章出處

記錄一下公司數據庫升級的步驟

公司的系統需要從1.0升級到1.1，包括所有正在使用我們公司產品的客戶，因為公司的客戶遍布全國，不可能出差每個客戶都跑一次

所以只能遠程協助的方式。我特意做了一個腳本，用電話指導客戶在SSMS里執行一下腳本就可以了

1.0的數據庫跟1.1的數據庫的區別是1.1的數據庫里的其中一個[CT_OuterCard]表比1.0的多了6個字段，其他所有表都一樣

還有存儲過程增加了很多，其他都沒有改變

---------------------------------------------華麗的分割線-----------------------------------------------

首先，先在公司的服務器數據庫上生成存儲過程腳本，數據庫是1.1版本的，下面的圖片里沒有說明的，都是默認設置，下一步即可

選中數據庫-》右鍵—》任務-》生成腳本

當然，如果你的數據庫里有自定義函數的話，也可以勾選函數，如果我們的數據庫沒有函數，所以。。。

保存到新建查詢窗口

---------------------------------------華麗的分割線-----------------------------------------------------

這一步做完了，然后編寫下面的SQL腳本

  1 --升級GPOS1.0到GPOS1.1數據庫的升級腳本 2013-7-4
  2 USE [GPOSDB]
  3 GO
  4 ------------------------------------刪除所有存儲過程-----------------------------------
  5 --select * from sys.procedures
  6 
  7 declare @sql varchar(4000)
  8 set @sql=''
  9 select @sql=@sql+'drop proc '+name+';   ' from sys.procedures
 10 --print @sql
 11 exec(@sql)
 12 
 13 --------------------------------在[CT_OuterCard]表添加6個字段-------------------------------
 14 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT  NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount]  DEFAULT ((0))
 15 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] [int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType]  DEFAULT ((0))
 16 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal]  DEFAULT ((0))
 17 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal]  DEFAULT ((0))
 18 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo]  DEFAULT ((0))
 19 ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate]  DEFAULT (getdate())
 20 --------------------------------------------------------------------------------------------------------------
 21 --把剛才在新建查詢窗口里生成的存儲過程腳本粘貼到下面
 22 ---------------------------創建GPOS1.1的所有存儲過程---------------------------------------------
 23 USE [GPOSDB]
 24 GO
 25 /****** 對象:  StoredProcedure [dbo].[Report_GreaserSaleStat]    腳本日期: 07/04/2013 13:27:09 ******/
 26 SET ANSI_NULLS OFF
 27 GO
 28 SET QUOTED_IDENTIFIER OFF
 29 GO
 30 
 31 
 32 CREATE PROC [dbo].[Report_GreaserSaleStat]
 33 @StartDate datetime,
 34 @EndDate datetime,
 35 @Action int --0為交易記錄，1為班次記錄
 36 
 37     
 38             insert into #tmpCardAmoutStat
 39                 (
 40                     VC_OC_CardNO,
 41             
 42             set @i=@i+1
 43         end
 44 
 45         truncate table #tmpCards
 46         insert into #tmpCards(VC_OC_CardNO)
 47         select VC_OC_CardNO from CT_OuterCard where isnull(VC_OC_Company,'')=''
 48         set @j=1
 49         select @cardcount=count(*) from #tmpCards
 50         while @j<=@cardcount
 51         begin
 52             select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j
 53             insert into #tmpCardAmoutStat
 54                 (
 55                     VC_OC_CardNO,
 56         
 57 
 58         insert into #tmpCardAmoutStat
 59             (
 60                 VC_OC_CardNO,
 61                 CompanyName,
 62                 VC_OC_UserName,
 63                 StartAmount,
 64                 FillMoney,
 65                 ConsumeSumVol, 
 66                 ConsumeMoney, 
 67                 SumConsumeSumVol,
 68                 SumConsumeMoney,
 69                 SumFillMoney
 70 
 71             )
 72         select 
 73             null,
 74             null,
 75             '客戶卡小計',
 76             sum(StartAmount),
 77             sum(FillMoney),
 78             sum(ConsumeSumVol),
 79         
 80         truncate table #tmpCards
 81         insert into #tmpCards(VC_OC_CardNO)
 82         select VC_IC_CardNO from CT_InhouseCard where isnull(VC_IC_CardNO,'')<>''
 83         set @j=1
 84         select @cardcount=count(*) from #tmpCards
 85         while @j<=@cardcount
 86         begin
 87             select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j
 88             insert into #tmpCardAmoutStat
 89                 (
 90                     VC_OC_CardNO,
 91                     CompanyName,
 92                     VC_OC_UserName,
 93                     StartAmount,
 94                     FillMoney,
 95                     ConsumeSumVol, 
 96                     ConsumeMoney, 
 97                     SumConsumeSumVol,
 98                     SumConsumeMoney,
 99                     SumFillMoney
100 
101                 )
102             select 
103                 @VC_OC_CardNO,
104                 '員工卡',
105                 isnull((select VC_IC_UserName from CT_InhouseCard where VC_IC_CardNO=@VC_OC_CardNO),''),
106                 isnull((select top 1 DE_FD_Amount from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime<=@StartDate) order by D_FD_DateTime desc),0),
107                 isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO and (D_A_AppendDateTime between @StartDate and @EndDate)),0),
108                 isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
109                 isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
110                 isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),
111                 isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),
112                 isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO),0)
113             set @j=@j+1
114         end
115 
116         insert into #tmpCardAmoutStat
117             (
118                 VC_OC_CardNO,
119                 CompanyName,
120                 VC_OC_UserName,
121                 StartAmount,
122                 FillMoney,
123                 ConsumeSumVol, 
124                 ConsumeMoney, 
125                 SumConsumeSumVol,
126                 SumConsumeMoney,
127                 SumFillMoney
128 
129             )
130         select 
131             null,
132             null,
133             '員工卡小計',
134             sum(StartAmount),
135             sum(FillMoney),
136             sum(ConsumeSumVol),
137             sum(ConsumeMoney),
138             sum(SumConsumeSumVol),
139             sum(SumConsumeMoney),
140             sum(SumFillMoney) 
141         from 
142             #tmpCardAmoutStat 
143         where 
144             CompanyName='員工卡'
145         ---計算員工卡匯總結束---
146     end
147     
148     ----計算總匯總開始---
149     insert into #tmpCardAmoutStat
150         (
151             VC_OC_CardNO,
152             CompanyName,
153             VC_OC_UserName,
154             StartAmount,
155             FillMoney,
156             ConsumeSumVol, 
157             ConsumeMoney, 
158             SumConsumeSumVol,
159             SumConsumeMoney,
160             SumFillMoney
161 
162         )
163     select 
164         null,
165         null,
166         '總計',
167         sum(StartAmount),
168         sum(FillMoney),
169         sum(ConsumeSumVol),
170         sum(ConsumeMoney),
171         sum(SumConsumeSumVol),
172         sum(SumConsumeMoney),
173         sum(SumFillMoney) 
174     from 
175         #tmpCardAmoutStat 
176     where 
177         (VC_OC_UserName='客戶卡小計' or VC_OC_UserName='員工卡小計') and VC_OC_CardNO is null
178     update #tmpCardAmoutStat set EndAmount=StartAmount+FillMoney-ConsumeMoney
179     ---計算總匯總結束---
180     select * from  #tmpCardAmoutStat
181 
182     drop table #tmpCards
183     drop table #tmpCompanys
184     drop table #tmpCardAmoutStat
185 GO
186 
187 --其他存儲過程省略。。。。。。。。。。。