SQLSERVER數據庫進級劇本圖文步調。本站提示廣大學習愛好者：（SQLSERVER數據庫進級劇本圖文步調）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLSERVER數據庫進級劇本圖文步調正文
只能長途協助的方法。我特地做了一個劇本，用德律風指點客戶在SSMS裡履行一下劇本便可以了
1.0的數據庫跟1.1的數據庫的差別是1.1的數據庫裡的個中一個[CT_OuterCard]表比1.0的多了6個字段，其他一切表都一樣
還有存儲進程增長了許多，其他都沒有轉變
起首，先在公司的辦事器數據庫上生成存儲進程劇本，數據庫是1.1版本的，上面的圖片裡沒有解釋的，都是默許設置，下一步便可
選中數據庫-》右鍵—》義務-》生成劇本
固然，假如你的數據庫裡有自界說函數的話，也能夠勾選函數，假如我們的數據庫沒有函數，所以。。。
保留到新建查詢窗口
這一步做完了，然後編寫上面的SQL劇本
--進級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
--其他存儲進程省略。。。。。。。。。。。
固然假如某些表的主鍵更改了也很簡略，應用alter table alter column語句修正一下便可以了
若有纰謬的處所，迎接年夜家拍磚o(∩_∩)o
