SQL Server database upgrade scripting steps _mssql

Only Remote Assistance in the way. I made a special script to use the phone to instruct the client to execute a script in SSMs.

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

There are a lot of stored procedures, and nothing else has changed.

First of all, in the company's server database to generate a stored procedure script, the database is 1.1 version, the following picture is not indicated, are the default settings, the next step can be

Select Database-"Right-" task-"Generate script"

Of course, if you have a custom function in your database, you can also check the function, if our database does not have a function, so ...

Save to new Query window

This step is done, and then write the following SQL script

Copy Code code as follows:

--Upgrade scripts for GPOS1.0 to GPOS1.1 databases 2013-7-4
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 6 fields to 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] (2) Not NULL CONSTRAINT [Df_ct_outercard_de_limittranstotal] DEFAULT ((0))
ALTER TABLE [dbo]. [Ct_outercard] ADD [de_limittranscurrtotal] [decimal] (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 ())
--------------------------------------------------------------------------------------------------------------
--paste the stored procedure script that you just created in the new Query window to the following
---------------------------create all stored procedures for 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 is a transaction, 1 is a 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 '
---Calculate the end of the employee card rollup---
End

----Calculate the total rollup start---
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
---Calculate total rollup End---
SELECT * FROM #tmpCardAmoutStat

drop table #tmpCards
drop table #tmpCompanys
drop table #tmpCardAmoutStat
Go

--Other stored procedures are omitted ...

Then send the script to the client and have the client execute it in the SSMs.

Of course, if some of the table's primary key changes are also very simple, use the ALTER TABLE ALTER COLUMN statement to modify it.

If there is a wrong place, welcome to shoot Bricks O (∩_∩) o