SQL Server Database Upgrade script steps

Only remote assistance is supported. I specially made a script to instruct the customer to execute the script in SSMS by phone.

The difference between a database of 1.0 and a database of 1.1 is that one [CT_OuterCard] Table in 1.1 has six more fields than that of 1.0, and all other tables are the same.

There is also a large increase in the storage process, and nothing else has changed

First, generate a stored procedure script on the company's server database. The database version is 1.1. The following figure does not show the default settings. Next step:

Right-click Database> task> Generate script

Of course, if your database contains a user-defined function, you can also select the function. If our database does not have a function, so...

Save to new query window

After completing this step, write the following SQL script

Copy codeThe Code is as follows:
-- Upgrade the GPOS1.0 to GPOS1.1 database with the upgrade script
USE [GPOSDB]
GO
------------------ Delete all stored procedures -------------------
-- Select * from sys. procedures

Declare @ SQL varchar (4000)
Set @ SQL =''
Select @ SQL = @ SQL + 'drop proc' + name + '; 'from sys. procedures
-- Print @ SQL
Exec (@ SQL)

------------------------------ Add six fields in the [CT_OuterCard] Table -------------------------------
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_limitdate] DEFAULT (getdate ())
Bytes --------------------------------------------------------------------------------------------------------------
-- Paste the Stored Procedure script generated in the new query window to the following
------------------------- Create all stored procedures of GPOS1.1 ---------------------------------------------
USE [GPOSDB]
GO
/***** Object: StoredProcedure [dbo]. [Report_GreaserSaleStat] script Date: 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 indicates the transaction record, and 1 indicates the shift record.

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,
'Customer card subtotal ',
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,
'Employee card ',
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,
'Employee card subtotal ',
Sum (StartAmount ),
Sum (FillMoney ),
Sum (ConsumeSumVol ),
Sum (ConsumeMoney ),
Sum (SumConsumeSumVol ),
Sum (SumConsumeMoney ),
Sum (SumFillMoney)
From
# TmpCardAmoutStat
Where
CompanyName = 'employee Card'
--- The worker Card Summary ends ---
End

---- Start of calculating the total summary ---
Insert into # tmpCardAmoutStat
(
VC_OC_CardNO,
CompanyName,
VC_OC_UserName,
StartAmount,
FillMoney,
ConsumeSumVol,
ConsumeMoney,
SumConsumeSumVol,
SumConsumeMoney,
SumFillMoney

)
Select
Null,
Null,
'Total ',
Sum (StartAmount ),
Sum (FillMoney ),
Sum (ConsumeSumVol ),
Sum (ConsumeMoney ),
Sum (SumConsumeSumVol ),
Sum (SumConsumeMoney ),
Sum (SumFillMoney)
From
# TmpCardAmoutStat
Where
(VC_OC_UserName = 'customer card subtotal 'or VC_OC_UserName = 'employee card subtotal') and VC_OC_CardNO is null
Update # tmpCardAmoutStat set EndAmount = StartAmount + FillMoney-ConsumeMoney
--- End of total computing summary ---
Select * from # tmpCardAmoutStat

Drop table # tmpCards
Drop table # tmpCompanys
Drop table # tmpCardAmoutStat
GO

-- Other stored procedures are omitted ...........

Then, send the script to the customer and let the customer execute it in SSMS.

Of course, if the primary key of some tables is changed, you can use the alter table alter column statement to modify it.

If anything is wrong, you are welcome to make a brick o