-- ==========================================================================\
--
-- PRESS CTRL + SHIFT + M AND SUPPLY THE APPROPRIATE VALUES
-- TO TURN THIS INTO A REAL SCRIPT.  EVEN THE COMMENTS MIGHT NOT MAKE
-- SENSE UNTIL YOU DO THIS.
--
-- ==========================================================================
--  Create Basic MIIS Delta View Components
-- ==========================================================================
--  Version:	1
--  Author:		Jerry Camel, Ensynch
--  Created:	December 12, 2007
-- ==========================================================================
--
-- Database:				<Database-Name, nvchar(50), Util>
-- Current Table or View:	<Current-Object, nvarchar(50), tCurrent>
-- Original Table:			<Original-Object, nvarchar(50), tOriginal>
-- Primary Key:				<Primary-Key, nvarchar(50), UniqueID>
-- Delta View:				<Delta-View, nvarchar(50), vDelta>
-- Post-Op Procedure:		<Post-Op-Proc, nvarchar(50), spDeltaPostOp>
--
-- ==========================================================================
--
-- WARNING!
--
-- The <Current-Object, nvarchar(50), tCurrent> table must already exist.
-- If a table object named <Original-Object, nvarchar(50), tOriginal> already exists, it will be dropped!
-- If a view named <Delta-View, nvarchar(50), vDelta> already exists, it will be dropped!
-- If a stored proc named <Post-Op-Proc, nvarchar(50), spDeltaPostOp> already exists, it will be dropped!
--
-- ==========================================================================

-- Temporary tracking table
IF OBJECT_ID('TempDB..#Tracking') IS NOT NULL DROP TABLE TempDB..#Tracking
CREATE TABLE #Tracking (VName nvarchar(25), VValue nvarchar(250))
GO

-- Specify the database to use
USE <Database-Name, nvchar(50), Util>
GO

-- Verify that <Current-Object, nvarchar(50), tCurrent> table exists.  If not, then exit.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[<Current-Object, nvarchar(50), tCurrent>]')
                                                  AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		PRINT CHAR(13) + 'ERROR: The current table, ''<Current-Object, nvarchar(50), tCurrent>'' does not exist.' +
			  CHAR(13) + 'Create it and run the script again.'
		INSERT INTO #Tracking VALUES('Error - Current', 'False')
		RETURN
	END
GO

-- Verify that spCopyTableData procedure exists.  If not, then create it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM #Tracking WHERE VName LIKE 'Error%') RETURN

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCopyTableData]')
                                              AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =======================================================================
-- Created from "Create Basic MIIS Delta View Components" Template v1
-- =======================================================================
-- Author:		Jerry Camel, Ensynch Inc.
-- Create date: 08/09/2007
-- Description:	Copies data from the specified Oracle table to a table
--				on the SQL Server.
--
-- Change List (Current version listed first):
--
-- Version		4
--  11/12/2007 - Jerry Camel
--  - Added in @PKOverride parameter to allow PK specification in cases
--    where PK can not be discerned automatically, such as using a table
--    through a linked server.  If @PKOverride is specified, auto PK
--    discovery is not performed.
--
--    @PKOverride is a string parameter contianing a comma delimited list
--    of the fields that should comprise the PK.
--
-- Version		3.1
--  11/02/2007 - Brad Turner
--  - Removed brackets from the table names.
--
-- Version		3.0
--  10/03/2007 - Jerry Camel
--  - Removed PKs as parameters in favor of auto PK discovery.
--
-- Version:		2.0
--  09/07/2007 - Jerry Camel
--  - Added ability to create PKs with more than one field.
--  - PK is now named as PK_[DestinationTable]
--
-- Version:		1.0
--  08/09/2007 - Jerry Camel
--  - Original Version
-- =======================================================================

CREATE PROCEDURE [dbo].[spCopyTableData]
	@SourceTable nvarchar(256),
	@DestinationTable nvarchar(256) = NULL,
	@PKOverride nvarchar(256) = NULL

AS
BEGIN

-- Create a comma delimited string of the columns that make up the PK from the source table.
DECLARE @PKString varchar(1000)
SELECT @PKString = NULL

IF @PKOverride IS NOT NULL
	SET @PKString = @PKOverride
ELSE
	SELECT @PKString = coalesce(@PKString + '', '', '''')  + C.name
	FROM sys.key_constraints AS K
			JOIN sys.tables AS T ON T.object_id = K.parent_object_id
				JOIN sys.schemas AS S ON S.schema_id = T.schema_id
					JOIN sys.index_columns AS I ON I.object_id = T.object_id AND I.index_id = K.unique_index_id
						JOIN sys.columns AS C ON C.object_id = T.object_id AND C.column_id = I.column_id
	WHERE T.name = @SourceTable AND K.type = ''PK''

-- Create dynamic SQL to create new table and select all rows into it.
-- Use PKString from above to specify the PK, not copied by SELECT INTO.

DECLARE @SQLString nvarchar(MAX)

SET @SQLString =	''USE [Util]'' + CHAR(13) + 
					''IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'''''' + @DestinationTable + '''''') AND type = ''''U'''')'' + CHAR(13) +
					''DROP TABLE '' + @DestinationTable + CHAR(13) + 
					''SELECT * INTO '' + @DestinationTable + '' FROM ''  + @SourceTable + CHAR(13)

	IF @PKString IS NOT NULL
		SET @SQLString = @SQLString + ''ALTER TABLE ['' + @DestinationTable + ''] ADD CONSTRAINT [PK_'' + @DestinationTable + ''] PRIMARY KEY('' + @PKString + '')''

EXEC(@SQLString)

END
' 
PRINT 'spCopyTableData stored procedure created.'
END
ELSE
PRINT 'spCopyTableData stored procedure already exists.  No need to create.'
GO

-- Drop, if necessary, and create <Original-Object, nvarchar(50), tOriginal> table.
IF EXISTS (SELECT * FROM #Tracking WHERE VName LIKE 'Error%') RETURN

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[<Original-Object, nvarchar(50), tOriginal>]')
                                              AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE <Original-Object, nvarchar(50), tOriginal>
		PRINT '<Original-Object, nvarchar(50), tOriginal> table dropped.'
	END

EXEC [dbo].[spCopyTableData] @SourceTable = N'<Current-Object, nvarchar(50), tCurrent>',
							 @DestinationTable = N'<Original-Object, nvarchar(50), tOriginal>'
PRINT '<Original-Object, nvarchar(50), tOriginal> table created.'
GO

-- Drop, if necessary, and create <Delta-View, nvarchar(50), vDelta> view.
IF EXISTS (SELECT * FROM #Tracking WHERE VName LIKE 'Error%') RETURN

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[<Delta-View, nvarchar(50), vDelta>]')
                                               AND OBJECTPROPERTY(id, N'IsView') = 1)
	BEGIN
		DROP VIEW <Delta-View, nvarchar(50), vDelta>
		PRINT '<Delta-View, nvarchar(50), vDelta> view dropped.'
	END

EXEC dbo.sp_executesql @statement = N'
-- =======================================================================
-- Created from "Create Basic MIIS Delta View Components" Template v1
-- =======================================================================
CREATE VIEW [<Delta-View, nvarchar(50), vDelta>] AS

	SELECT		C.*, ''ADD'' AS DELTA_STATUS
	FROM		<Current-Object, nvarchar(50), tCurrent> AS C LEFT OUTER JOIN
					<Original-Object, nvarchar(50), tOriginal> AS O
						ON C.<Primary-Key, nvarchar(50), UniqueID> = O.<Primary-Key, nvarchar(50), UniqueID>
	WHERE		(O.<Primary-Key, nvarchar(50), UniqueID> IS NULL)

UNION

	SELECT		O.*, ''DELETE'' AS DELTA_STATUS
	FROM		<Current-Object, nvarchar(50), tCurrent> AS C RIGHT OUTER JOIN
					<Original-Object, nvarchar(50), tOriginal> AS O
						ON C.<Primary-Key, nvarchar(50), UniqueID>  = O.<Primary-Key, nvarchar(50), UniqueID>
	WHERE		(C.<Primary-Key, nvarchar(50), UniqueID> IS NULL)

UNION

	SELECT		C.*, ''MODIFY'' AS DELTA_STATUS
	FROM		<Current-Object, nvarchar(50), tCurrent> AS C INNER JOIN
					(SELECT	<Primary-Key, nvarchar(50), UniqueID>
					 FROM	(SELECT *
							 FROM <Current-Object, nvarchar(50), tCurrent>
							
							 UNION

							 SELECT *
							 FROM <Original-Object, nvarchar(50), tOriginal>) AS U
					 GROUP BY <Primary-Key, nvarchar(50), UniqueID>
					 HAVING COUNT(*) > 1) AS MODS
						ON C.<Primary-Key, nvarchar(50), UniqueID> = MODS.<Primary-Key, nvarchar(50), UniqueID>'

PRINT '<Delta-View, nvarchar(50), vDelta> view created.'
GO

-- Drop, if necessary and create <Post-Op-Proc, nvarchar(50), spDeltaPostOp> stored procedure.
IF EXISTS (SELECT * FROM #Tracking WHERE VName LIKE 'Error%') RETURN

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[<Post-Op-Proc, nvarchar(50), spDeltaPostOp>]')
                                               AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		DROP PROCEDURE <Post-Op-Proc, nvarchar(50), spDeltaPostOp>
		PRINT '<Post-Op-Proc, nvarchar(50), spDeltaPostOp> stored procedure dropped.'
	END

EXEC dbo.sp_executesql @statement = N'
-- =======================================================================
-- Created from "Create Basic MIIS Delta View Components" Template v1
-- =======================================================================
CREATE PROCEDURE <Post-Op-Proc, nvarchar(50), spDeltaPostOp> 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	EXEC [dbo].[spCopyTableData] @SourceTable = N''<Current-Object, nvarchar(50), tCurrent>'',
                                 @DestinationTable = N''<Original-Object, nvarchar(50), tOriginal>''
END'
PRINT '<Post-Op-Proc, nvarchar(50), spDeltaPostOp> stored procedure created.'
GO