mysql+网站升级mssql_SQLSERVER数据库升级脚本图文步骤

--升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4

USE [GPOSDB]

GO

------------------删除所有存储过程-------------------

--select * from sys.procedures

declare @sql varchar(4000)

set @sql=''

select @sql=@sql+'drop proc '+name+';   ' from sys.procedures

--print @sql

exec(@sql)

--------------------------------在[CT_OuterCard]表添加6个字段-------------------------------

ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT  NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount]  DEFAULT ((0))

ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] [int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType]  DEFAULT ((0))

ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal]  DEFAULT ((0))

ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal]  DEFAULT ((0))

ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo]  DEFAULT ((0))

ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate]  DEFAULT (getdate())

--------------------------------------------------------------------------------------------------------------

--把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面

---------------------------创建GPOS1.1的所有存储过程---------------------------------------------

USE [GPOSDB]

GO

/****** 对象:  StoredProcedure [dbo].[Report_GreaserSaleStat]    脚本日期: 07/04/2013 13:27:09 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROC [dbo].[Report_GreaserSaleStat]

@StartDate datetime,

@EndDate datetime,

@Action int --0为交易记录，1为班次记录

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

set @i=@i+1

end

truncate table #tmpCards

insert into #tmpCards(VC_OC_CardNO)

select VC_OC_CardNO from CT_OuterCard where isnull(VC_OC_Company,'')=''

set @j=1

select @cardcount=count(*) from #tmpCards

while @j<=@cardcount

begin

select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

CompanyName,

VC_OC_UserName,

StartAmount,

FillMoney,

ConsumeSumVol,

ConsumeMoney,

SumConsumeSumVol,

SumConsumeMoney,

SumFillMoney

)

select

null,

null,

'客户卡小计',

sum(StartAmount),

sum(FillMoney),

sum(ConsumeSumVol),

truncate table #tmpCards

insert into #tmpCards(VC_OC_CardNO)

select VC_IC_CardNO from CT_InhouseCard where isnull(VC_IC_CardNO,'')<>''

set @j=1

select @cardcount=count(*) from #tmpCards

while @j<=@cardcount

begin

select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

CompanyName,

VC_OC_UserName,

StartAmount,

FillMoney,

ConsumeSumVol,

ConsumeMoney,

SumConsumeSumVol,

SumConsumeMoney,

SumFillMoney

)

select

@VC_OC_CardNO,

'员工卡',

isnull((select VC_IC_UserName from CT_InhouseCard where VC_IC_CardNO=@VC_OC_CardNO),''),

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),

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),

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),

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),

isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),

isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),

isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO),0)

set @j=@j+1

end

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

CompanyName,

VC_OC_UserName,

StartAmount,

FillMoney,

ConsumeSumVol,

ConsumeMoney,

SumConsumeSumVol,

SumConsumeMoney,

SumFillMoney

)

select

null,

null,

'员工卡小计',

sum(StartAmount),

sum(FillMoney),

sum(ConsumeSumVol),

sum(ConsumeMoney),

sum(SumConsumeSumVol),

sum(SumConsumeMoney),

sum(SumFillMoney)

from

#tmpCardAmoutStat

where

CompanyName='员工卡'

---计算员工卡汇总结束---

end

----计算总汇总开始---

insert into #tmpCardAmoutStat

(

VC_OC_CardNO,

CompanyName,

VC_OC_UserName,

StartAmount,

FillMoney,

ConsumeSumVol,

ConsumeMoney,

SumConsumeSumVol,

SumConsumeMoney,

SumFillMoney

)

select

null,

null,

'总计',

sum(StartAmount),

sum(FillMoney),

sum(ConsumeSumVol),

sum(ConsumeMoney),

sum(SumConsumeSumVol),

sum(SumConsumeMoney),

sum(SumFillMoney)

from

#tmpCardAmoutStat

where

(VC_OC_UserName='客户卡小计' or VC_OC_UserName='员工卡小计') and VC_OC_CardNO is null

update #tmpCardAmoutStat set EndAmount=StartAmount+FillMoney-ConsumeMoney

---计算总汇总结束---

select * from  #tmpCardAmoutStat

drop table #tmpCards

drop table #tmpCompanys

drop table #tmpCardAmoutStat

GO

--其他存储过程省略。。。。。。。。。。。