SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UTIL_GenerateArchiveProcedureDDL]
/*
	CODE GENERATOR.
		The procedure starts by loading table and column metadata into table variables, @Tables and @Columns.
		The body of the procedure iterates the set of @Tables and builds up the output code.
		Each line of generated code is inserted into table variable @Sql.  
		The procedure ends by selecting all lines from @Sql in the order they were inserted.

	HOW TO USE
		Run this procedure in the Source (non-warehouse) database.
		MINIMAL:
			 exec generateArchiveProcedure @WarehouseDatabaseName = 'WarehouseDatabaseName'

		SPECIFIC TABLE (a schema can be specified if necessary)
			exec GenerateArchiveProcedure @WarehouseDatabaseName = 'WarehouseDB',
				@SourceTable = 'MyTableName'

		SPECIFY PROCEDURE NAME
			exec GeneratearchiveProcedure @WarehouseDatabaseName = 'WarehouseDB',
				@ProcedureName = 'MyWarehouseUpdateProcedureName'

		in standard Results-to-Grid mode (line lengths are too short on Text mode by default).
		Select the entire output, copy and paste into a new text editor document.

		NOTE: If line breaks are missing, open SSMS Tools->Options,  Query Results->SQL Server->Results to Grid
			  and tick "Retail CR/LF on copy or save".  (reopen the procedure window).

	PARAMETERS
		@WarehouseDBName				Name of the warehousedatabase.
		@ProcedureSchema  (Optional)    Schema for generaged procedure. Default: "dbo".
		@ProcedureName	  (Optional)	Name for generated procedure. Default: "UpdateDataWarehouse".
		@SourceSchema     (Optional)    Include every table in specifed schema; Null for all schemas. Default: Null.
		@SourceTable	  (Optional)    Include only table(s) matching specified name; Null for all tables. Default: Null.

    TABLE SELECTION CRITERIA
        All tables 

	RETURNS
		The generated code as a rowset.
		
 
    LIMITATIONS
        All source tables must have an explicit primary key.

	VERSIONS
		17/01/2017 Andrew Ing First Draft
		20/01/2017 Andrew Ing Corrected error with tables with only primary keys.

*/
	@WarehouseDatabaseName nvarchar(100)	= NULL,
	@ProcedureSchema nvarchar(100)			= 'dbo',
	@ProcedureName nvarchar(100)			= 'UpdateDataWarehouse',
	@SourceSchema nvarchar(100)				= NULL,
	@SourceTable nvarchar(100)				= NULL

AS

BEGIN TRY

DECLARE @TableNbr int
DECLARE @Schema varchar(200), @Table varchar(200), @PrimaryKey varchar(200), @TableObjectId int
DECLARE @ProcedureHead varchar(8000);
DECLARE @MergeOutputTableName varchar(200); 
 
SET XACT_ABORT, NOCOUNT ON;

-- Parameter Checking
IF (@WarehouseDatabaseName IS NULL) RAISERROR('You must provide the Warehouse database name as first parameter (@WarehouseDatabaseName)', 16, 1);

DECLARE @Tables TABLE (
    id int IDENTITY(1,1)
    ,table_object_id int
    ,schema_name varchar(200)
    ,table_name varchar(200)
    ,primary_key varchar(200)
)

DECLARE @Columns TABLE(
	id int IDENTITY(1,1)
	,table_object_id int NOT NULL
	,column_name varchar(200)
	,nullable_flag char(1)
	,condensed_type varchar(50)
	,primary_key_flag char(1) NOT NULL
	,identity_flag char(1) NOT NULL
	,computed_column_flag char(1) NOT NULL
	,null_definition varchar(10) NOT NULL
)
 
-- Table to hold text.
	DECLARE @Sql TABLE(
		 id int IDENTITY
		,sq varchar(max))

-- Get all tables.
	INSERT INTO @Tables (
		 table_object_id
		,schema_name
		,table_name)
	SELECT
		 O.object_id
		,S.name
		,O.name 
	FROM sys.objects O
	 INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
		WHERE
			O.type = 'U'
			AND (@SourceSchema IS NULL OR S.name = @SourceSchema)
			AND (@SourceTable IS NULL OR O.name = @SourceTable)
			AND O.object_id IN (SELECT KC.parent_object_id FROM sys.key_constraints KC WHERE KC.type = 'PK' )
			AND O.name not in ('ELMAH_Error',
								'tblLookupChanelsByBundle',
								'tblLookupExtraHierarchy',
								'tblLookupExtrasByBundle',
								'tblProductSearchCache',
								'tblProductSearchCacheData',
								'ShopProductUnitTest')
		ORDER BY S.name, O.name

-- Insert column details
	INSERT INTO @Columns (table_object_id, column_name, nullable_flag, condensed_type, primary_key_flag, identity_flag, computed_column_flag, null_definition)
	SELECT T.table_object_id
		,C.name
		,CASE WHEN C.is_nullable = 1 THEN 'Y' ELSE 'N' END
		,type_name(C.user_type_id) + CASE WHEN (type_name(C.user_type_id) IN('varchar',  'char'))
			then '(' + cast(C.max_length as varchar) + ')' 
		WHEN (type_name(C.user_type_id) IN('nvarchar', 'nchar'))
			then '(' + cast(C.max_length / 2 as varchar) + ')' 
		WHEN type_name(C.user_type_id) = 'decimal'  
			then '(' + cast(C.[precision] as varchar) + ',' + cast(C.scale as varchar)   + ')' 
		ELSE '' END
		,ISNULL(PK.IsPrimaryKey, 'N')
		,CASE WHEN C.is_identity = 1 THEN 'Y' ELSE 'N' END
		,CASE WHEN C.is_computed = 1 THEN 'Y' ELSE 'N' END
		,CASE WHEN C.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
	FROM sys.columns C INNER JOIN @Tables T ON C.object_id = T.table_object_id
		LEFT OUTER JOIN 
			(SELECT ic.column_id, ic.object_id, 'Y' AS IsPrimaryKey FROM 
			sys.index_columns IC 
			JOIN sys.key_constraints KC ON KC.parent_object_id = ic.object_id AND IC.index_id = KC.unique_index_id
			WHERE KC.type = 'PK') PK ON PK.object_id = C.object_id AND PK.column_id = C.column_id
		LEFT OUTER JOIN (
			SELECT P.minor_id AS column_id, P.major_id AS object_id FROM sys.extended_properties P 
			WHERE P.name IN ('TEMPORARY_COLUMN') ) EX
			ON EX.object_id = T.table_object_id AND EX.column_id = C.column_id
		WHERE EX.column_id IS  NULL
	ORDER BY C.object_id

    -- Fix up Varchar(MAX), which are generated as Varchar(-1)
	UPDATE @Columns SET condensed_type = REPLACE(condensed_type, 'varchar(-1)', 'varchar(max)') WHERE condensed_type LIKE '%varchar(-1)';
	UPDATE @Columns SET condensed_type = REPLACE(condensed_type, 'varchar(0)', 'varchar(max)') WHERE condensed_type LIKE '%varchar(0)';
/*
	Generate SQL Text.
*/ 
SET @ProcedureHead = '
CREATE PROCEDURE ' + QUOTENAME(@ProcedureSchema) + '.' + QUOTENAME(@ProcedureName) +'
/*
	** DO NOT EDIT. 
	** Auto-generated by procedure {PROCEDURE_NAME} ON {GENERATION_DATE}.

	PURPOSE
		This procedure updates an archive warehouse database with the current values in the source database.
		The warehouse database must contain the same tables with the same columns plus temporal (StartDate, 
		EndDate) columns.		

	CODE GENERATOR HISTORY
		18/01/2017 Andrew Ing 1.02.

*/
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY

DECLARE @EffectiveDate datetime;
DECLARE @ObsoletedRowCount int;
DECLARE @InsertedRowCount int;
DECLARE @UpdatedRowCount int;
DECLARE @TableActionSummary varchar(1000);
DECLARE @DebugInfo varchar(2000);

SET @EffectiveDate = GETDATE();
';

SELECT @ProcedureHead = REPLACE(@ProcedureHead, '{PROCEDURE_NAME}', ISNULL(OBJECT_NAME(@@PROCID), ''));
SELECT @ProcedureHead = REPLACE(@ProcedureHead, '{GENERATION_DATE}', CONVERT(varchar(20), GETDATE()));

INSERT INTO @Sql (sq) VALUES 
    (@ProcedureHead),
    ('')
 
SET @TableNbr = 1
WHILE (@TableNbr <= (SELECT MAX(id) FROM @Tables) )
BEGIN
    SELECT 
		@Schema = QUOTENAME(schema_name), 
		@Table = QUOTENAME(table_name), 
		@PrimaryKey = QUOTENAME(primary_key), 
		@MergeOutputTableName = QUOTENAME('#MergeOutput_' + table_name),
		@TableObjectId = table_object_id
    FROM @Tables 
	WHERE id = @TableNbr
 
    -- Start Table-specific Code  --------------------------
	-- DECLARE TABLE VARIABLE TO HOLD MERGE OUTPUT.
    INSERT INTO @sql (sq) VALUES
	 ('-------------------------------------------------------------'),
	 ('-- TABLE: ' + @Schema + '.' + @Table),
	 (''),
	 ('  SET @DebugInfo = ''Create Temp Table ' + @MergeOutputTableName + ''';'),
	 ('  CREATE TABLE ' + @MergeOutputTableName +' ('),
	 ('    MergeAction nvarchar(10)')
	INSERT INTO @Sql (sq)
		SELECT  '   ,'  +
			QUOTENAME(C.column_name) + ' ' + C.condensed_type + ' NULL'
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;

	 INSERT INTO @sql (sq) VALUES
	 ('  );'),
	 (''),
	 ('  SET @DebugInfo = ''Merge ' + @Schema + '.' + @Table + ''';'),
	 ('  BEGIN TRANSACTION;'),
	 -- BEGIN MERGE STATEMENT.
	 ('  -- Select only Current (EndDate IS NULL) warehouse rows for the comparison.'),
	 ('  WITH TargetCTE AS'),
	 ('    (SELECT * '),
	 ('     FROM ' + @WarehouseDatabaseName + '.' + @Schema + '.' + @Table),
	 ('     WHERE EndDate IS NULL'),
	 ('    )'),
	 ('  MERGE  TargetCTE AS [Target]'),
	 ('  USING  ' + @Schema + '.' + @Table + ' AS [Source]'),
	 ('    ON' );

	-- MERGE JOIN - all Primary Key columns.
	INSERT INTO @Sql (sq)
		SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY C.id) = 1 THEN '         ' ELSE '     AND ' END +
			'[Target].' + QUOTENAME(C.column_name) + ' = [Source].' + QUOTENAME(C.column_name) 
		FROM @Columns C 
		WHERE 
			C.table_object_id = @TableObjectId 
			AND C.primary_key_flag='Y'
		ORDER BY C.id;

		-- MERGE MATCHED Clause
		-- A row (with same primary key) exists in both tables
		-- This is not required if the table contains only primary keys.
	IF ( (SELECT COUNT(*) FROM @Columns C WHERE C.table_object_id = @TableObjectId AND C.primary_key_flag = 'N') > 0 )
		BEGIN
			INSERT INTO @sql (sq) VALUES
				('  WHEN MATCHED'),
				('    AND EXISTS ('),
				('      SELECT')
			INSERT INTO @Sql (sq)
				SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY C.id) = 1 THEN '          ' ELSE '         ,' END +
					'[Target].' + QUOTENAME(C.column_name)
				FROM @Columns C 
				WHERE 
					C.table_object_id = @TableObjectId 
					AND C.primary_key_flag = 'N'
				ORDER BY C.id;	
			INSERT INTO @SQL (sq) VALUES
				('      EXCEPT'),
				('      SELECT')
			INSERT INTO @Sql (sq)
				SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY C.id) = 1 THEN '          ' ELSE '         ,' END +
					'[Source].' + QUOTENAME(C.column_name)
				FROM @Columns C 
				WHERE 
					C.table_object_id = @TableObjectId 
					AND C.primary_key_flag = 'N'
				ORDER BY C.id;	
			INSERT INTO @SQL (sq) VALUES
				('    )'),
				('    THEN  -- Record has changed.'),
				('      UPDATE'),
				('      SET [EndDate] = @EffectiveDate')
		END
	INSERT INTO @SQL (sq) VALUES
		('  WHEN NOT MATCHED BY SOURCE'),
		('    THEN  -- Record deleted from source.'),
		('      UPDATE'),
		('      SET [EndDate] = @EffectiveDate'),
		('  WHEN NOT MATCHED BY TARGET'),
		('    THEN  -- New Record.'),
		('      INSERT ('),
		('        [StartDate]')
	INSERT INTO @Sql (sq)
		SELECT '       ,' +
			QUOTENAME(C.column_name)
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;
	INSERT INTO @Sql (sq) VALUES
		('       ) VALUES ('),
		('        @EffectiveDate')
	INSERT INTO @Sql (sq)
		SELECT  '       ,' +
			'[Source].' + QUOTENAME(C.column_name)
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;
	INSERT INTO @Sql (sq) VALUES
		('       )'),
		('  OUTPUT'),
		('     $Action AS MergeAction')
	INSERT INTO @Sql (sq)
		SELECT  '     ,' +
			'[Source].' + QUOTENAME(C.column_name)
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;
	INSERT INTO @Sql (sq) VALUES
		('  INTO ' + @MergeOutputTableName + ';'),
		(''),
		('  -- Insert new rows for records that have changed (old rows were marked as expired by'),
		('  -- the first MATCHED clause. #MergeOutput_ has been populated only with Source columns'),
		('  -- but MERGE is a full outer join so exclude rows with a Null primary key column.'),
		('  SET @DebugInfo = ''Insert for changed rows ' + @Schema + '.' + @Table + ''';'),
		('  INSERT INTO ' + @WarehouseDatabaseName + '.' + @Schema + '.' + @Table ),
		('    ('),
		('      [StartDate]')
	INSERT INTO @Sql (sq)
		SELECT  
			'     ,' + QUOTENAME(C.column_name)
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;
	INSERT INTO @Sql (sq) VALUES
		('    )'),
		('    SELECT'),
		('      @EffectiveDate')
	INSERT INTO @Sql (sq)
		SELECT  
			'     ,' + QUOTENAME(C.column_name)
		FROM @Columns C WHERE C.table_object_id = @TableObjectId 
		ORDER BY C.id;
	INSERT INTO @Sql (sq) VALUES
		('    FROM ' + @MergeOutputTableName ),
		('    WHERE'),
		('      MergeAction = ''UPDATE'''),
		('      AND ' + (SELECT TOP 1 QUOTENAME(column_name) FROM @Columns WHERE primary_key_flag = 'Y' AND table_object_id = @TableObjectId) + 
				' IS NOT NULL;'),
		('    SELECT @UpdatedRowCount = @@ROWCOUNT;'),
		(''),
		('  COMMIT TRANSACTION'),
		(''),
		('  SET @DebugInfo = ''Get Row Counts ' + @Schema + '.' + @Table + ''';'),
		('  SELECT'),
		('    @ObsoletedRowCount = SUM(CASE WHEN MergeAction = ''UPDATE'' THEN 1 ELSE 0 END),'),
		('    @InsertedRowCount = SUM(CASE WHEN MergeAction = ''INSERT'' THEN 1 ELSE 0 END)'),
		('  FROM'),
		('    ' + @MergeOutputTableName +';'),
		(''),
		('  SET @TableActionSummary = CONVERT(varchar(19), GETDATE(), 121) + '' ' + @Schema + '.' + @Table + '
			 '' + CAST(ISNULL(@ObsoletedRowCount, 0) AS varchar(10)) + '' Rows Obsoleted, '' + 
			 CAST(ISNULL(@InsertedRowCount, 0) AS varchar(10)) + '' New Rows Inserted, '' +
			 CAST(@UpdatedRowCount AS varchar(10)) + '' Update Rows Inserted.'';'),
		('  PRINT @TableActionSummary;'),
		('  DROP TABLE ' + @MergeOutputTableName + ';'),
		(''),
		('-- END OF TABLE ' + @Schema + '.' + @Table),
		('');


 
 
    SET @TableNbr = @TableNbr + 1
END
 
 
 
INSERT INTO @sql (sq) VALUES
     (''),
	 ('END TRY'),
	 ('BEGIN CATCH'),
	 ('  EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo;'),
	 ('  RETURN 5555;'),
	 ('END CATCH'),
     ('/*'),
     ('  END OF GENERATED SCRIPT'),
     ('*/'),
	 ('GO')

-- END OF GENERATOR.
    
 
SELECT sq FROM @Sql ORDER BY id

END TRY
BEGIN CATCH
	EXECUTE dbo.ERROR_HandleCaughtError 
	RETURN 5555
END CATCH