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
--其他存储过程省略。。。。。。。。。。。
然后把这个脚本发给客户，让客户在ssms里执行一下就可以了
当然如果某些表的主键更改了也很简单，使用alter table alter column语句修改一下就可以了
如有不对的地方，欢迎大家拍砖o(∩_∩)o