安云网 - AnYun.ORG | 专注于网络信息收集、网络数据分享、网络安全研究、网络各种猎奇八卦。
当前位置: 安云网 > 技术关注 > DataBase > MSSQLSERVER > SQLSERVER数据库升级脚本图文步骤_MsSql

SQLSERVER数据库升级脚本图文步骤_MsSql

时间:2014-04-27来源: 作者:点击:
只能远程协助的方式。我特意做了一个脚本,用电话指导客户在SSMS里执行一下脚本就可以了 1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[CT_OuterCard]表比1.0的多了6个字段,其他所

只能远程协助的方式。我特意做了一个脚本,用电话指导客户在SSMS里执行一下脚本就可以了

//安云网咨询系统

1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[CT_OuterCard]表比1.0的多了6个字段,其他所有表都一样 //内容来自AnYun.ORG

还有存储过程增加了很多,其他都没有改变 //安云网咨询系统

首先,先在公司的服务器数据库上生成存储过程脚本,数据库是1.1版本的,下面的图片里没有说明的,都是默认设置,下一步即可 //内容来自AnYun.ORG

选中数据库-》右键—》任务-》生成脚本

//copyright AnYun.ORG

//安云网,anyun.org

//内容来自安云网

当然,如果你的数据库里有自定义函数的话,也可以勾选函数,如果我们的数据库没有函数,所以。。。

//ANYUN.ORG

//内容来自AnYun.ORG

//安云网,anyun.org

保存到新建查询窗口 //copyright AnYun.ORG

//本文来自安云网

这一步做完了,然后编写下面的SQL脚本

//本文来自安云网

复制代码 代码如下:

--升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4
USE [GPOSDB]
GO
------------------删除所有存储过程-------------------
--select * from sys.procedures
//copyright AnYun.ORG

declare @sql varchar(4000)
set @sql=''
select @[email protected]+'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())
-------------------------------------------------------------------------------------------------------------- //内容来自AnYun.ORG
--把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面
---------------------------创建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

//copyright AnYun.ORG


CREATE PROC [dbo].[Report_GreaserSaleStat]
@StartDate datetime,
@EndDate datetime,
@Action int --0为交易记录,1为班次记录 //copyright AnYun.ORG

   
            insert into #tmpCardAmoutStat
                (
                    VC_OC_CardNO,

            set @[email protected]+1
        end

//安云网,anyun.org

        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<[email protected]
        begin
            select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  [email protected]
            insert into #tmpCardAmoutStat
                (
                    VC_OC_CardNO,
        //本文来自安云网

        insert into #tmpCardAmoutStat
            (
                VC_OC_CardNO,
                CompanyName,
                VC_OC_UserName,
                StartAmount,
                FillMoney,
                ConsumeSumVol,
                ConsumeMoney,
                SumConsumeSumVol,
                SumConsumeMoney, //安云网咨询系统
                SumFillMoney //内容来自AnYun.ORG

            )
        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 //安云网,anyun.org
        while @j<[email protected]
        begin
            select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  [email protected]
            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 [email protected]_OC_CardNO),''),
                isnull((select top 1 DE_FD_Amount from CT_FuelingData where [email protected]_OC_CardNO and (D_FD_DateTime<[email protected]) order by D_FD_DateTime desc),0),
                isnull((select sum(DE_A_AppendAmount) from CT_Append where [email protected]_OC_CardNO and (D_A_AppendDateTime between @StartDate and @EndDate)),0), //本文来自安云网
                isnull((select sum(DE_FD_Volume) from CT_FuelingData where [email protected]_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
                isnull((select sum(DE_FD_Amount) from CT_FuelingData where [email protected]_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
                isnull((select sum(DE_FD_Volume) from CT_FuelingData where [email protected]_OC_CardNO),0),
                isnull((select sum(DE_FD_Amount) from CT_FuelingData where [email protected]_OC_CardNO),0),
                isnull((select sum(DE_A_AppendAmount) from CT_Append where [email protected]_OC_CardNO),0) //本文来自安云网
            set @[email protected]+1
        end

//ANYUN.ORG

        insert into #tmpCardAmoutStat
            (
                VC_OC_CardNO,
                CompanyName,
                VC_OC_UserName,
                StartAmount,
                FillMoney,
                ConsumeSumVol,
                ConsumeMoney,
                SumConsumeSumVol,
                SumConsumeMoney,

//内容来自安云网


                SumFillMoney

//内容来自AnYun.ORG

            )
        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, //copyright AnYun.ORG
            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 //ANYUN.ORG

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

//内容来自AnYun.ORG



然后把这个脚本发给客户,让客户在SSMS里执行一下就可以了

当然如果某些表的主键更改了也很简单,使用alter table alter column语句修改一下就可以了

//安云网咨询系统

如有不对的地方,欢迎大家拍砖o(∩_∩)o //ANYUN.ORG

顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
验证码: 点击我更换图片
相关内容
推荐内容