Chunk Transfer

CREATE         PROCEDURE sp_chunk_transfer
        @Source_DatabaseFullName varchar(50), 
        @Destination_DatabaseFullName varchar(50), 
        @BatchSize INT,
        @DelayLength smallint,
@AllowableLogPercentage TINYINT,
@Update_Flag bit
AS

/*
Author: Richard S. Hale
Business Intelligence

Inputs:
@Source_DatabaseFullName  Ex. 'MarketingDB.dbo.tbl_to_update_from'
@Destination_DatabaseFullName  Ex. 'MarketingDB.dbo.tbl_to_update_to'
@BatchSizeInteger Value for Batch Size Rec [ 100,000 to 250,000 ] Ex. '100000'
@DelayLengthDelay in Seconds Rec [ 5 ] Ex. '5'
@AllowableLogPercentagePercentage [1-100] allowable log space percentage Ex. '40'
@Update_FlagEx. ['1' for Update, '0' for Insert]


Specifications:
For Chunking Inserts and Updates in a Replication Environment
Designed to simply be a production push. WORKING around replication 
struggles with bandwidth.

Requirements:
1. Source and destination tables must exist.
2. Source and destination tables must have the same schema for insert.
3. Destination table must have a primary key.
4. The source table's primary key constraint will be reset to match destination.
4a. Meaning the source table must have the primary key of the destination within it for updates.
4b. This allows for dynamic delete join and quicker processing.
5. There are no transformations within this process. 

Modifications:
September 11th, 2003:

Removed '_WORKED' table all together. Which allows for smaller volume joins between WORKING and the source
table for deletes and inserts. Only set back is that the only way to watch progress is to watch the source
table shrink. 
Want to look at applying primary Key to WORKING table between updates if the batch is over a certain size.
See Microsoft Best Practices for indexes.

February 2nd, 2004:

Dynamically Handles Indentity Inserts. As long as you don't violate Uniqueness Constraints.
*/
SET NOCOUNT ON
--SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- DYNAMIC SCRIPT VARIABLES
DECLARE @InsertToWORKING VARCHAR(4000)
DECLARE @InsertToDestination VARCHAR(4000)
DECLARE @InsertColumnList VARCHAR(4000)
DECLARE @UpdatetoDestination VARCHAR(8000)
DECLARE @DeleteJoinWORKING VARCHAR(4000)
DECLARE @DeleteJoinWORKING_Staging VARCHAR(4000)
DECLARE @PKDestination VARCHAR(255)
DECLARE @PKIndentifier VARCHAR(255)
DECLARE @PKDropScript_Source VARCHAR(2000)
DECLARE @PKCreationScript_Source VARCHAR(2000)
DECLARE @PKCreationScript_WORKING VARCHAR(2000)
DECLARE @PKDropScript_WORKING VARCHAR(2000)
DECLARE @PKDropScript VARCHAR(2000)
DECLARE @PK_Flag BIT
DECLARE @PK_Flag_Source BIT
DECLARE @PK_Loop_Count TINYINT
DECLARE @PKCreationScript VARCHAR(8000)
DECLARE @DynamicCursorText VARCHAR(8000)
DECLARE @DynamicCursorText_PK VARCHAR(8000)
DECLARE @DynamicCursorText_PK_Source VARCHAR(2000)
DECLARE @TableDef_DynamicCursorText VARCHAR(4000)
DECLARE @TableDef_ReturnVal TINYINT
DECLARE @ErrorText VARCHAR(4000)
DECLARE @ErrorCheck VARCHAR(4000)
DECLARE @IdentityTestScript VARCHAR(2000)
DECLARE @IDENT_CURRENT BIGINT

-- VARIABLES FROM CREATE TABLE SCRIPTS
DECLARE @StrBuildWORKINGTable VARCHAR(8000)
DECLARE@StrDropWORKINGTable VARCHAR(8000) 
DECLARE@StrSchemaCheck VARCHAR(6000) 
DECLARE@COLUMN_NAME VARCHAR(100)
DECLARE@DATA_TYPE VARCHAR(100)
DECLARE@CHARACTER_MAXIMUM_LENGTH VARCHAR(100)
DECLARE@NUMERIC_PRECISION VARCHAR(100)
DECLARE@NUMERIC_SCALE VARCHAR(100)
DECLARE@COLLATION_NAME VARCHAR(100)
DECLARE @IS_NULLABLE VARCHAR(100)

-- CONTROL LOOP VARIABLES
DECLARE @RecordTotal INT 
DECLARE @LoopCount INT
DECLARE @CurrCount INT
DECLARE @LogSpaceUsed TINYINT
DECLARE @StagingTableName VARCHAR(255)
DECLARE @Source_DatabaseName VARCHAR(255)
DECLARE @Destination_DatabaseName VARCHAR(255)
DECLARE @Source_TableName VARCHAR(255)
DECLARE @Destination_TableName VARCHAR(255)

-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--         SYSTEM DEFINED SETTINGS
SET @PK_Flag = 0
SET @PK_Flag_Source = 0
SET @CurrCount = 0
        SET @Source_DatabaseName = SUBSTRING(@Source_DatabaseFullName, 0, CHARINDEX('.', @Source_DatabaseFullName))
SET @Source_TableName = SUBSTRING(@Source_DatabaseFullName, CHARINDEX('.dbo.', @Source_DatabaseFullName) + 5, LEN(@Source_DatabaseFullName)-CHARINDEX('.dbo.', @Source_DatabaseFullName) + 5)
        SET @Destination_DatabaseName = SUBSTRING(@Destination_DatabaseFullName, 0, CHARINDEX('.', @Destination_DatabaseFullName))
SET @Destination_TableName = SUBSTRING(@Destination_DatabaseFullName, CHARINDEX('.dbo.', @Destination_DatabaseFullName) + 5, LEN(@Destination_DatabaseFullName)-CHARINDEX('.dbo.', @Destination_DatabaseFullName) + 5)

SET @ErrorCheck = 'IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '''+@Source_DatabaseName+''')
BEGIN
RAISERROR (''The source database does not exist.'', 16, 1)
RETURN
END'
EXEC(@ErrorCheck)

SET @ErrorCheck = 'if NOT EXISTS (SELECT * FROM '+ @Source_DatabaseName +'.dbo.sysobjects WHERE name='''+@Source_TableName+''') 
BEGIN
RAISERROR (''The source table does not exist in the source database.'', 16, 1)
RETURN
END'

EXEC(@ErrorCheck)

SET @ErrorCheck = 'IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '''+@Destination_DatabaseName+''')
BEGIN
RAISERROR (''The destination database does not exist.'', 16, 1)
RETURN
END'
EXEC(@ErrorCheck)

SET @ErrorCheck = 'if NOT EXISTS (SELECT * FROM '+ @Source_DatabaseName +'.dbo.sysobjects WHERE name='''+@Destination_TableName+''') 
BEGIN
RAISERROR (''The destination table does not exist in the source database.'', 16, 1)
RETURN
END'
EXEC(@ErrorCheck)

EXEC('USE '+@Source_DatabaseName)

        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
        --        DYNAMIC CURSOR FOR ROW COUNT RETRIEVAL OF DYNAMIC TABLE NAME
        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
                SET @DynamicCursorText = 'SELECT count(*) AS THECOUNT INTO ##ROWCOUNT FROM '+@Source_DatabaseFullName
                EXEC (@DynamicCursorText)
                SET @RecordTotal = (SELECT TOP 1 THECOUNT FROM ##ROWCOUNT)
                EXEC('DROP TABLE ##ROWCOUNT')

        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
        SET @LoopCount = (@RecordTotal/@BatchSize)

        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  CREATE WORKING TABLES
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
SET @StrDropWORKINGTable = 
'if exists (select * from '+@Source_DatabaseName+'.dbo.sysobjects where name = '''+ @Source_TableName + '_WORKING'')'
+' drop table '+ @Source_DatabaseFullName + '_WORKING'

        
        EXEC (@StrDropWORKINGTable)

SET @UpdatetoDestination = 'UPDATE ' + @Destination_DatabaseFullName + ' SET '
SET @StrBuildWORKINGTable = 'CREATE TABLE ' + @Source_DatabaseFullName + '_WORKING (' 
SET @InsertColumnList = '('
SET @TableDef_DynamicCursorText = 'DECLARE TableDef CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLLATION_NAME FROM '+ @Source_DatabaseName +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@Source_TableName +''' ORDER BY ORDINAL_POSITION ASC'
EXEC (@TableDef_DynamicCursorText)
OPEN TableDef
FETCH NEXT FROM TableDef INTO @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH, @NUMERIC_PRECISION, @NUMERIC_SCALE, @COLLATION_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @InsertColumnList = @InsertColumnList + @COLUMN_NAME  +', '
IF @DATA_TYPE IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR', 'BINARY', 'VARBINARY')
BEGIN
SET @StrBuildWORKINGTable = @StrBuildWORKINGTable + @COLUMN_NAME + ' ' + @DATA_TYPE + '(' + @CHARACTER_MAXIMUM_LENGTH + ')'
SET @UpdatetoDestination = @UpdatetoDestination + ' ' + @COLUMN_NAME + ' = WORKING.'+ @COLUMN_NAME + ','
END
IF @DATA_TYPE IN ('DECIMAL', 'NUMERIC')
BEGIN
SET @StrBuildWORKINGTable = @StrBuildWORKINGTable + @COLUMN_NAME + ' ' + @DATA_TYPE + '(' + @NUMERIC_PRECISION + ', ' + @NUMERIC_SCALE + ')'
SET @UpdatetoDestination = @UpdatetoDestination + ' ' + @COLUMN_NAME + ' = WORKING.'+ @COLUMN_NAME + ','
END
IF @DATA_TYPE IN ('IMAGE', 'TEXT', 'NTEXT', 'TINYINT', 'SMALLINT', 'INT', 'BIGINT', 
'DATETIME', 'SMALLDATETIME', 'BIT', 'FLOAT', 'REAL', 'MONEY', 'SMALLMONEY', 
'TIMESTAMP', 'UNIQUEIDENTIFIER')
BEGIN
SET @StrBuildWORKINGTable = @StrBuildWORKINGTable + @COLUMN_NAME + ' ' + @DATA_TYPE
SET @UpdatetoDestination = @UpdatetoDestination + ' ' + @COLUMN_NAME + ' = WORKING.'+ @COLUMN_NAME + ','
END
FETCH NEXT FROM TableDef INTO @COLUMN_NAME, @DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH, @NUMERIC_PRECISION, @NUMERIC_SCALE, @COLLATION_NAME
IF @@FETCH_STATUS = 0
BEGIN
SET @StrBuildWORKINGTable = @StrBuildWORKINGTable + ', '
END
ELSE
BEGIN
SET @StrBuildWORKINGTable = @StrBuildWORKINGTable + ')'
SET @InsertColumnList = LEFT(@InsertColumnList, LEN(@InsertColumnList) - 1) + ')'
SET @UpdatetoDestination = left(@UpdatetoDestination, len(@UpdatetoDestination)-1) + ' FROM ' + @Source_DatabaseFullName + '_WORKING WORKING INNER JOIN ' + @Destination_DatabaseFullName + ' DESTINATION ON '
END

END
CLOSE TableDef
DEALLOCATE TableDef


        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  VERIFY IDENTICAL SCHEMAS BETWEEN SOURCE AND DESTINATION
--Using the Information Schema View we Compare the Two Tables for differences in
--variable size, variable type, and Collation
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

        SET @StrSchemaCheck = '
        SELECT 
                COLUMN_NAME, 
                DATA_TYPE, 
                CHARACTER_MAXIMUM_LENGTH, 
                NUMERIC_PRECISION, 
                NUMERIC_SCALE, 
                COLLATION_NAME 
        INTO ##IStest_1
        FROM 
                '+@Source_DatabaseName+'.INFORMATION_SCHEMA.COLUMNS
        WHERE 
                TABLE_NAME = '''+@Source_TableName+''' 
         ORDER BY ORDINAL_POSITION ASC

        SELECT 
                COLUMN_NAME, 
                DATA_TYPE, 
                CHARACTER_MAXIMUM_LENGTH, 
                NUMERIC_PRECISION, 
                NUMERIC_SCALE, 
                        COLLATION_NAME 
        INTO ##IStest_2
        FROM 
                '+@Destination_DatabaseName+'.INFORMATION_SCHEMA.COLUMNS 
        WHERE         
                TABLE_NAME = '''+@Destination_TableName+'''
 ORDER BY ORDINAL_POSITION ASC
                
        SELECT 
                ''1'' nonmatch_exists
                INTO ##NOMATCH
        FROM 
                ##IStest_1  edwc_DIM_CHANNEL
                        LEFT OUTER JOIN ##IStest_2  edwc_DIM_CHANNEL_2
                        ON  (ISNULL(edwc_DIM_CHANNEL.COLUMN_NAME, ''-64'') = ISNULL(edwc_DIM_CHANNEL_2.COLUMN_NAME, ''-64''))
                        AND (ISNULL(edwc_DIM_CHANNEL.DATA_TYPE, ''-64'') = ISNULL(edwc_DIM_CHANNEL_2.DATA_TYPE, ''-64'')) 
                        AND (ISNULL(edwc_DIM_CHANNEL.COLLATION_NAME, ''-64'') = ISNULL(edwc_DIM_CHANNEL_2.COLLATION_NAME, ''-64''))
        WHERE 
                edwc_DIM_CHANNEL_2.COLUMN_NAME is null'
        EXEC(@StrSchemaCheck) 
      
        SET @TableDef_ReturnVal = (Select top 1 nonmatch_exists from ##NOMATCH)
        SET @StrSchemaCheck = 'drop table ##IStest_1
        drop table ##IStest_2
        drop table ##NOMATCH'
        EXEC(@StrSchemaCheck) 

if (@TableDef_ReturnVal IS NOT null)
BEGIN
RAISERROR ('The Source Schema does not match the Destination Schema.', 16, 1)
RETURN
END


        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  VERIFY DESTINATION DATABASE PRIMARY KEY AND CREATE DROP RECREATE AND DELETE JOIN SCRIPTS
        --  Modified Thursday September 11th 2003 -R.Hale
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+


SET @PKCreationScript = 'ALTER TABLE '+@Source_DatabaseFullName + ' ADD '
SET @PKDropScript = 'ALTER TABLE '+ @Source_DatabaseFullName +' DROP CONSTRAINT '
SET @DeleteJoinWORKING = 'DELETE ' + @Source_DatabaseFullName + '_WORKING'
SET @DeleteJoinWORKING_Staging = 'DELETE ' + @Source_DatabaseFullName + ' FROM ' + @Source_DatabaseFullName + ' STAGING INNER JOIN '+ @Source_DatabaseFullName + '_WORKING WORKING ON '


SET @DynamicCursorText_PK = 'DECLARE PK_CURSOR CURSOR FOR 
SELECT 
        column_name name,
                constraint_name name
FROM 
'+@destination_DatabaseName+'.information_schema.key_column_usage
WHERE
constraint_catalog='''+@destination_DatabaseName +'''
                and table_name = '''+@destination_TableName+'''
        ORDER BY 
                constraint_name, ordinal_position'
EXEC (@DynamicCursorText_PK)
        OPEN PK_CURSOR
        FETCH NEXT FROM PK_CURSOR INTO @PKDESTINATION, @PKIndentifier 
WHILE (@@fetch_status = 0)
BEGIN
SET @PK_Flag = 1
IF (@PKCreationScript = 'ALTER TABLE '+@Source_DatabaseFullName + ' ADD ')
BEGIN
SET @PKCreationScript = @PKCreationScript + ' PRIMARY KEY CLUSTERED (['+ @PKDESTINATION
SET @PKCreationScript_Source = @PKCreationScript_Source + ' PRIMARY KEY CLUSTERED (['+ @PKDESTINATION
SET @PKCreationScript_WORKING = @PKCreationScript_WORKING + ' PRIMARY KEY CLUSTERED (['+ @PKDESTINATION
SET @PKDropScript = @PKDropScript + @PKIndentifier
                        SET @UpdatetoDestination = replace(@UpdatetoDestination, ' '+@PKDESTINATION+' = WORKING.'+@PKDESTINATION+',', '')
                        SET @UpdatetoDestination = replace(@UpdatetoDestination, ' '+@PKDESTINATION+' = WORKING.'+@PKDESTINATION, '')
SET @UpdatetoDestination = @UpdatetoDestination + ' WORKING.'+ @PKDESTINATION +' = DESTINATION.'+ @PKDESTINATION +' and '
SET @DeleteJoinWORKING_Staging = @DeleteJoinWORKING_Staging + ' WORKING.'+ @PKDESTINATION +' = STAGING.'+ @PKDESTINATION +' and '
END 
ELSE
BEGIN
SET @PKCreationScript = @PKCreationScript + ',' + @PKDESTINATION
END
        FETCH NEXT FROM PK_CURSOR INTO @PKDESTINATION, @PKIndentifier
END
        CLOSE PK_CURSOR
        DEALLOCATE PK_CURSOR

SET @PKCreationScript = @PKCreationScript +  '])'


IF ( @PK_Flag <> 1)
BEGIN
RAISERROR ('The destination table does not have a primary key.', 16, 1)
RETURN
END 
        ELSE
        BEGIN
        SET @UpdatetoDestination = left(@UpdatetoDestination, LEN(@UpdatetoDestination)-4)       
        SET @DeleteJoinWORKING_Staging = left(LTRIM(RTRIM(@DeleteJoinWORKING_Staging)), LEN(LTRIM(RTRIM(@DeleteJoinWORKING_Staging)))-4)
        END

        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  Prepare Drop PK on Source Table
        --  Modified Thursday September 11th 2003 -R.Hale
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+

SET @PKDESTINATION = ''
SET @PKIndentifier = ''

        SET @PKCreationScript_WORKING = replace(@PKCreationScript, @Source_DatabaseFullName, @Source_DatabaseFullName+ '_WORKING')
SET @PKCreationScript_Source = @PKCreationScript
        SET @PKDropScript_WORKING = replace(@PKDropScript_WORKING, @Source_DatabaseFullName, @Source_DatabaseFullName+ '_WORKING')
        SET @PKDropScript_Source = 'ALTER TABLE '+ @Source_DatabaseFullName +' DROP CONSTRAINT '


SET @DynamicCursorText_PK_Source = '
        SELECT 
 constraint_name name
        INTO ##PK_CURSOR_SOURCE
        FROM 
'+@source_DatabaseName+'.information_schema.key_column_usage
WHERE
constraint_catalog='''+@source_DatabaseName +'''
                and table_name = '''+@source_TableName+'''
        ORDER BY 
                constraint_name, ordinal_position'
EXEC (@DynamicCursorText_PK_Source)
        SET @PKDESTINATION = (Select top 1 [name] from ##PK_CURSOR_SOURCE)
SET @PKDropScript_SOURCE = @PKDropScript_SOURCE + @PKDESTINATION
        EXEC('DROP TABLE ##PK_CURSOR_SOURCE')

IF (@PKDestination = '')
BEGIN
SET @PK_Flag_Source = 1
END
        -- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  EXECUTE DYNAMIC SCRIPTS
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+


IF @PK_Flag <> 1
BEGIN
RAISERROR ('The Source table does not have a unique field for the destination primary key.', 16, 1)
RETURN
END
ELSE
BEGIN
EXEC (@StrBuildWORKINGTable)
IF (@PK_Flag_Source <> 1)
BEGIN
EXEC (@PKDropScript_Source)
END
EXEC (@PKCreationScript_Source)
END

-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--         Current Identity Count Check 'Easy Way to Check for Identity'
        --  Modified Thursday September 11th 2003 -R.Hale
        
        SET @IdentityTestScript = '
        SELECT 
                IDENT_CURRENT('''+ @Destination_DatabaseFullName +''') current_count
        INTO ##TEMP_IDENTITY_CHECK'
        EXEC(@IdentityTestScript)
        SET @IDENT_CURRENT = (Select top 1 current_count from ##TEMP_IDENTITY_CHECK)
        EXEC('DROP TABLE ##TEMP_IDENTITY_CHECK')

       
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--         CONTROL LOOP

        WHILE (@CurrCount <= @LoopCount)
        BEGIN

                CREATE TABLE #LOGSPACE (
                   Database_Name varchar(30),
                   Log_Size_MB decimal,
                   Log_Space_Used_Perc decimal,
                   Status bit
                   )
                -- Execute the command, putting the results in the table
                INSERT INTO #LOGSPACE
                   EXEC ('DBCC SQLPERF(LOGSPACE) WITH No_INFOMSGS')

                -- Display the results
                SET @LogSpaceUsed = (SELECT top 1 Log_Space_Used_Perc FROM #LOGSPACE where Database_name = @Source_DatabaseName)
drop table #LOGSPACE
                IF (@LogSpaceUsed <= @AllowableLogPercentage)
                BEGIN             

                        IF (@CurrCount = @LoopCount) 
                        BEGIN
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC INSERT INTO WORKING FROM STAGING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
SET @InsertToWORKING = 'INSERT INTO '+@Source_DatabaseFullName+'_WORKING 
SELECT * FROM '+@Source_DatabaseFullName+''
EXEC(@InsertToWORKING)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer Source to WORKING. Batch number '+@CurrCount+' of '+(@LoopCount + 1)+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
IF (@UPDATE_FLAG = 0)
BEGIN
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC INSERT INTO DESTINATION FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
IF (@IDENT_CURRENT is not null)
        BEGIN
SET @InsertToDestination = 'SET IDENTITY_INSERT '+ @Destination_DatabaseFullName +' ON  
INSERT INTO '+@Destination_DatabaseFullName+' '+ @InsertColumnList+ ' 
SELECT * FROM '+@Source_DatabaseFullName+'_WORKING 
    SET IDENTITY_INSERT '+ @Destination_DatabaseFullName +' OFF'
EXEC(@InsertToDestination)
END
ELSE
BEGIN
SET @InsertToDestination = 'INSERT INTO '+@Destination_DatabaseFullName+'
SELECT * FROM '+@Source_DatabaseFullName+'_WORKING '
END
EXEC(@InsertToDestination)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer WORKING to Destination. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
ELSE IF (@UPDATE_FLAG = 1)
BEGIN
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC UPDATE INTO DESTINATION FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@UpdatetoDestination)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer WORKING to Destination. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC DELETE JOIN FROM WORKING TO STAGING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@DeleteJoinWORKING_Staging)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Delete from Staging from WORKING. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC DELETE JOIN FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@DeleteJoinWORKING)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Delete from WORKING. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
                        END
                        ELSE
                        BEGIN
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC INSERT INTO WORKING FROM STAGING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
SET @InsertToWORKING = 'INSERT INTO '+@Source_DatabaseFullName+'_WORKING 
SELECT TOP '+ CONVERT(VARCHAR,@BatchSize) +' * FROM '+@Source_DatabaseFullName+''
EXEC(@InsertToWORKING)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer Source to WORKING. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
IF (@UPDATE_FLAG = 0)
BEGIN

-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC INSERT INTO DESTINATION FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
IF (@IDENT_CURRENT is not null)
        BEGIN
SET @InsertToDestination = 'SET IDENTITY_INSERT '+ @Destination_DatabaseFullName +' ON  
INSERT INTO '+@Destination_DatabaseFullName+' '+ @InsertColumnList+ ' 
SELECT * FROM '+@Source_DatabaseFullName+'_WORKING 
    SET IDENTITY_INSERT '+ @Destination_DatabaseFullName +' OFF'
EXEC(@InsertToDestination)
END
ELSE
BEGIN
SET @InsertToDestination = 'INSERT INTO '+@Destination_DatabaseFullName+'
SELECT * FROM '+@Source_DatabaseFullName+'_WORKING '
EXEC(@InsertToDestination)
END

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer WORKING to Destination. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
ELSE IF (@UPDATE_FLAG = 1)
BEGIN
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC UPDATE INTO DESTINATION FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@UpdatetoDestination)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Transfer WORKING to Destination. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC DELETE JOIN FROM WORKING TO STAGING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@DeleteJoinWORKING_Staging)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Delete from Staging from WORKING. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
--DYNAMIC DELETE JOIN FROM WORKING
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
BEGIN TRANSACTION
EXEC(@DeleteJoinWORKING)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
set @ErrorText ='Transactional Error During Delete from WORKING. Batch number '+CONVERT(VARCHAR,@CurrCount)+' of '+(CONVERT(VARCHAR,@LoopCount + 1))+'.' 
RAISERROR (@ErrorText, 16, 1)
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END                                                                                                                  
                        END
                        SET @CurrCount = @CurrCount + 1  
                END
                ELSE
                BEGIN
                        PRINT @LogSpaceUsed
                        WAITFOR DELAY @DelayLength
                END
        
        END

-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--  WORKING AND WORKED TABLE CLEANUP
-- +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
EXEC (@StrDropWORKINGTable)














GO