快捷導(dǎo)航

SQLSERVER數(shù)據(jù)庫(kù)升級(jí)腳本圖文步驟

更新時(shí)間：2013年11月24日 15:34:43 作者：

公司的系統(tǒng)需要從1.0升級(jí)到1.1，包括所有正在使用我們公司產(chǎn)品的客戶(hù)，因?yàn)楣镜目蛻?hù)遍布全國(guó)，就寫(xiě)了一個(gè)SQLSERVER數(shù)據(jù)庫(kù)遠(yuǎn)程升級(jí)腳本圖文步驟,大家參考使用吧

只能遠(yuǎn)程協(xié)助的方式。我特意做了一個(gè)腳本，用電話指導(dǎo)客戶(hù)在SSMS里執(zhí)行一下腳本就可以了

1.0的數(shù)據(jù)庫(kù)跟1.1的數(shù)據(jù)庫(kù)的區(qū)別是1.1的數(shù)據(jù)庫(kù)里的其中一個(gè)[CT_OuterCard]表比1.0的多了6個(gè)字段，其他所有表都一樣

還有存儲(chǔ)過(guò)程增加了很多，其他都沒(méi)有改變

首先，先在公司的服務(wù)器數(shù)據(jù)庫(kù)上生成存儲(chǔ)過(guò)程腳本，數(shù)據(jù)庫(kù)是1.1版本的，下面的圖片里沒(méi)有說(shuō)明的，都是默認(rèn)設(shè)置，下一步即可

選中數(shù)據(jù)庫(kù)-》右鍵—》任務(wù)-》生成腳本

當(dāng)然，如果你的數(shù)據(jù)庫(kù)里有自定義函數(shù)的話，也可以勾選函數(shù)，如果我們的數(shù)據(jù)庫(kù)沒(méi)有函數(shù)，所以。。。

保存到新建查詢(xún)窗口

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

復(fù)制代碼代碼如下:

--升級(jí)GPOS1.0到GPOS1.1數(shù)據(jù)庫(kù)的升級(jí)腳本 2013-7-4
USE [GPOSDB]
GO
------------------刪除所有存儲(chǔ)過(guò)程-------------------
--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個(gè)字段-------------------------------
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())
--------------------------------------------------------------------------------------------------------------
--把剛才在新建查詢(xún)窗口里生成的存儲(chǔ)過(guò)程腳本粘貼到下面
---------------------------創(chuàng)建GPOS1.1的所有存儲(chǔ)過(guò)程---------------------------------------------
USE [GPOSDB]
GO
/****** 對(duì)象: 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,
            '客戶(hù)卡小計(jì)',
            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,
            '員工卡小計(jì)',
            sum(StartAmount),
            sum(FillMoney),
            sum(ConsumeSumVol),
            sum(ConsumeMoney),
            sum(SumConsumeSumVol),
            sum(SumConsumeMoney),
            sum(SumFillMoney)
        from
            #tmpCardAmoutStat
        where
            CompanyName='員工卡'
        ---計(jì)算員工卡匯總結(jié)束---
    end

    ----計(jì)算總匯總開(kāi)始---
    insert into #tmpCardAmoutStat
        (
            VC_OC_CardNO,
            CompanyName,
            VC_OC_UserName,
            StartAmount,
            FillMoney,
            ConsumeSumVol,
            ConsumeMoney,
            SumConsumeSumVol,
            SumConsumeMoney,
            SumFillMoney

        )
    select
        null,
        null,
        '總計(jì)',
        sum(StartAmount),
        sum(FillMoney),
        sum(ConsumeSumVol),
        sum(ConsumeMoney),
        sum(SumConsumeSumVol),
        sum(SumConsumeMoney),
        sum(SumFillMoney)
    from
        #tmpCardAmoutStat
    where
        (VC_OC_UserName='客戶(hù)卡小計(jì)' or VC_OC_UserName='員工卡小計(jì)') and VC_OC_CardNO is null
    update #tmpCardAmoutStat set EndAmount=StartAmount+FillMoney-ConsumeMoney
    ---計(jì)算總匯總結(jié)束---
    select * from #tmpCardAmoutStat