欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-10-20 11:46:57
只能远程协助的方式。我特意做了一个脚本,用电话指导客户在ssms里执行一下脚本就可以了 1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[ct_outer...

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

1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[ct_outercard]表比1.0的多了6个字段,其他所有表都一样

还有存储过程增加了很多,其他都没有改变

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

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

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

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

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

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

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

保存到新建查询窗口

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

这一步做完了,然后编写下面的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

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



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

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

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