Chunk Transfer

,

Work table method for transferring any table across replication in usable transactional chunks. Very useful for publishers requiring bulk insert operations on source tables.

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'
	@BatchSize			Integer Value for Batch Size Rec [ 100,000 to 250,000 ] Ex. '100000'
	@DelayLength			Delay in Seconds Rec [ 5 ] Ex. '5'
	@AllowableLogPercentage		Percentage [1-100] allowable log space percentage Ex. '40'
	@Update_Flag			Ex. ['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

Rate

Share

Share

Rate