-- =======================================================================
-- Author:		Jerry Camel, Ensynch Inc.
-- Create date: 12/14/2007
-- Description:	Creates an attribute level delta table for the given
--				Current and Original tables
--
-- Change List (Current version listed first):
--
-- Version:		2
--  12/17/2007 - Jerry Camel
--  - Corrected colum search query to pull from tables & views, not
--    just tables.
--
-- Version:		1
--  12/14/2007 - Jerry Camel
--  - Original Version
-- =======================================================================
CREATE PROCEDURE [dbo].[spCreateAttributeLevelDeltaTable]
	@KeyColumn nvarchar(MAX),
	@CurrentTable nvarchar(MAX),
	@OriginalTable nvarchar(MAX),
	@DeltaTable nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Make sure our temp tables can be created without conflict.
	IF OBJECT_ID('TempDB..tempModified') IS NOT NULL DROP TABLE TempDB..tempModified
	IF OBJECT_ID('TempDB..tempModifiedValues') IS NOT NULL DROP TABLE TempDB..tempModifiedValues
	EXEC('IF OBJECT_ID(''' + @DeltaTable + ''') IS NOT NULL DROP TABLE ' + @DeltaTable)

	--PRINT 'Create tempModified from @CurrentTable to force the proper column types.'
	EXEC('SELECT ' + @KeyColumn + '
		  INTO TempDB..tempModified
		  FROM ' + @CurrentTable + ' WHERE 1=0')

	--PRINT 'Create an index of modified rows.'
	EXEC('INSERT INTO TempDB..tempModified
		  SELECT ' + @KeyColumn + ' 
		  FROM (SELECT * FROM ' + @CurrentTable + ' UNION SELECT * FROM ' + @OriginalTable + ') AS U
		  GROUP BY ' + @KeyColumn + '
		  HAVING COUNT(*) > 1')

	--PRINT 'Create tempModifiedValues from @CurrentTable to force the proper column types.'
	EXEC('SELECT ' + @KeyColumn + ' AS KeyValue,
				 CAST(NULL AS nvarchar(128)) AS ColumnName,
				 CAST(NULL AS nvarchar(MAX)) AS CurrentValue, 
				 CAST(NULL AS nvarchar(MAX)) AS OriginalValue
		  INTO TempDB..tempModifiedValues
		  FROM ' + @CurrentTable + '
		  WHERE 1=0')

	--PRINT 'Creating column loop cursor...'
	DECLARE @ColName nvarchar(128)
	DECLARE ColList CURSOR FOR
		(SELECT C.[name]
		 FROM sys.columns C JOIN
			  (SELECT [name], [object_id] FROM sys.tables
			   UNION
			   SELECT [name], [object_id] FROM sys.views) T on C.[object_id] = T.[object_id]
		 WHERE T.[name] = @CurrentTable AND
	 		   C.[name] <> @KeyColumn)

	OPEN ColList
	FETCH NEXT FROM ColList INTO @ColName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--Print 'Analyzing column, ' + @ColName + '...'
		EXEC('INSERT INTO TempDB..tempModifiedValues (KeyValue, ColumnName, CurrentValue, OriginalValue) 
			  SELECT M.' + @KeyColumn + ', ''' + @ColName + ''', C.' + @ColName + ', O.' + @ColName + '
			  FROM TempDB..tempModified M JOIN
				' + @CurrentTable + ' C ON C.' + @KeyColumn + ' = M.' + @KeyColumn + ' JOIN
				' + @OriginalTable + ' O ON O.' + @KeyColumn + ' = M.' + @KeyColumn + '
			WHERE CAST(C.' + @ColName + ' as nvarchar(MAX)) <> CAST(O.' + @ColName + ' as nvarchar(MAX))')
		FETCH NEXT FROM ColList INTO @ColName
	END
	CLOSE ColList
	DEALLOCATE ColList

	--PRINT 'Creating delta table structure...'
	EXEC('SELECT *,
				 CAST(NULL AS nvarchar(50)) AS DELTA_STATUS,
				 CAST(NULL AS nvarchar(50)) AS ATTRIBUTE_NAME
		  INTO ' + @DeltaTable + '
		  FROM ' + @CurrentTable + ' WHERE 1=0')

	--PRINT 'Inserting delta adds into delta table...'
	EXEC('INSERT INTO ' + @DeltaTable + ' SELECT C.*, ''ADD'' AS DELTA_STATUS, NULL AS ATTRIBUTE_NAME
		  FROM ' + @CurrentTable + ' AS C LEFT OUTER JOIN
		  ' + @OriginalTable + ' AS O
		  ON C.' + @KeyColumn + ' = O.' + @KeyColumn + '
		  WHERE (O.' + @KeyColumn + ' IS NULL)')

	--PRINT 'Inserting delta deletes into delta table...'
	EXEC('INSERT INTO ' + @DeltaTable + ' SELECT O.*, ''DELETE'' AS DELTA_STATUS, NULL AS ATTRIBUTE_NAME
		  FROM ' + @CurrentTable + ' AS C RIGHT OUTER JOIN
		  ' + @OriginalTable + ' AS O
		  ON C.' + @KeyColumn + ' = O.' + @KeyColumn + '
		  WHERE (C.' + @KeyColumn + ' IS NULL)')

	--PRINT 'Inserting delta attribute level modifications into delta table...'
	EXEC('INSERT INTO ' + @DeltaTable + ' SELECT C.*, ''MODIFY_ATTRIBUTE'' AS DELTA_STATUS, M.ColumnName AS ATTRIBUTE_NAME
		  FROM ' + @CurrentTable + ' AS C JOIN
		  TempDB..tempModifiedValues AS M
		  ON C.' + @KeyColumn + ' = M.KeyValue')

	DROP TABLE TempDB..tempModified
	DROP TABLE TempDB..tempModifiedValues
END