The CAP SQL Upgrade (Create)Process combines CAP portal database change package scripts into one setup script
Change package scripts are collected in alpha order based on the match pattern:CAPDatabase_5?CP??_??.sql
It Can be called like this: "d:\Scripts\CAPDbScriptsCombiner.bat" "d:\Scripts\"
	Where d:\Scripts\ - path to sql scripts folder


ALL the file names are collected FIRST, then the files are merged
This prevents multiple, serial file copies of duplicate data


The file names are collected using the search pattern CAPDatabase_5?CP??_??.sql

CAPDatabase_00CP00_00.sql contains the common declares, opens a transaction, and opens the try block
and is used to start the single script file

The closing logic is contained in the CAPDatabase_95CP99_99.sql file which updates the schema version, performs the CATCH, and ends the transaction

The new logic allows one to simply ADD the new code to the existing "CP" file, as the internal logic keeps track
of the update sequence numbers, and only the highest is writen to the DB when the scripts are executed

All the code is surrounded by a try/catch block, so the entire upgrade will either suceed or fail completely. There should
be no partial completions.

****	ADDING NEW CODE	****
A new file is ONLY NEEDED when a new Release is created, ie. 56R1->56R2, not on a per sprint basis.
The sprint numbers are no longer needed, so the file for an entire release could be named:
CAPDatabase_56CP01_01.sql.
If a post release hot fix were needed, it could be added as CAPDatabase_56CP01_02.sql, which would preserve the original file.

Otherwise the new code is simply appended in a block like this:

-- start
SET @CurrentDatabaseChangeNumber += 1
SET @CurrentDatabaseVersion = @CAEVersion + '.' + RIGHT(N'00'+CAST(@CurrentReleaseNumber AS varchar(2)),2) + N'.' + RIGHT(N'00'+CAST(@CurrentSprintNumber AS varchar(2)),2)  + N'.' + RIGHT(N'00'+CAST(@CurrentDatabaseChangeNumber AS varchar(4)),2)

IF (@SCHEMA_VERSION < @CurrentDatabaseVersion) -- DO this Upgrade
BEGIN
--	SQL code goes here
END
-- end

There is no need to update or fetch the schema version for each update, as only the starting version number matters for the execution sequence
and only the last version needs to be written to the database for the next update.