Autogenerate Merge Statement
CREATE PROCEDURE [dbo].[usp_utl_WriteMerge1]
(
@DstSchema VARCHAR(255)
, @DstTable VARCHAR(255)
)
AS
/*
/*=======================================================================================================================================
-- Author :Mihir Shah
-- Created :04/07/2018
-- Purpose/Description
-- This stored procedure generates creation script for a stored procedure containing Merge statments to load data from
source to dimension (destination).
=========================================================================================================================================
Usage:
EXEC [dbo].[usp_utl_WriteMerge1]
@DstSchema = 'trn'
, @DstTable = 'C_Client';
==========================================================================================================================================*/
Notes:
- Add mapping information between source and destination in dbo.DimDataMap
- ColumnType determines the type of the column. The following are possible values:
SK for Surrogate Key
NK for Natural Key
type0 for a type 0 attribute
type1 for a type 1 attribute
type2 for a type 2 attribute
HK for house keeping columns (RowStartDate, RowEndDate, IsRowActive)
- Type 2 dimensions must include the following housekeeping columns
RowStartDate DateTime NOT NULL
RowEndDate DateTime NULL
IsRowActive Bit NOT NULL
- Type 1 or Type 0 dimensions shoudn't include these housekeeping columns
- If dimension is a type0 SCD, merge statment is generated to
only inserts new records. Existing records will not be updated.
- If it's a type1 SCD, merge statement is generated to
insert new records and update existing records.
- If the dimension contains both type1 and type2 attributes,
merge statement is generated to handle inserts and updtaes accordingly.
==========================================================================================================================================
*/
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
/* declare variables: src schema and table name */
DECLARE @SrcSchema VARCHAR(255)
, @SrcTable VARCHAR(255);
/* check table existence in data map */
IF
(
SELECT COUNT(*)
FROM dbo.DimDataMap
WHERE DstTable = @DstTable
AND DstSchema = @DstSchema
) = 0
BEGIN
PRINT 'Hmm...There is no mapping information for ' + @DstSchema + '.' + @DstTable + CHAR(10)
+ ' in dbo.DimDataMap.';
END;
/* check table existence in destination database*/
IF NOT EXISTS
(
SELECT s.name + '.' + t.name
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = @DstSchema
AND t.name = @DstTable
)
BEGIN
PRINT CHAR(10) + 'Not again, you know why it didn''t work?' + CHAR(10) + @DstSchema + '.' + @DstTable
+ ' doesn''t exist in ' + DB_NAME() + ' database. That''s why.' + CHAR(10)
+ 'It''s ok though, you get three lives.';
END;
/* identify merge type */
DECLARE @MergeType VARCHAR(255);
IF OBJECT_ID('tempdb..#types') IS NOT NULL
DROP TABLE #types;
SELECT DISTINCT
[ColumnType]
INTO #types
FROM dbo.DimDataMap
WHERE DstSchema = @DstSchema
AND DstTable = @DstTable
AND ColumnType IN ('type0', 'type1', 'type2');
IF
(
SELECT COUNT(*)FROM #types
) = 1
BEGIN
IF
(
SELECT [ColumnType] FROM #types
) = 'type0'
SET @MergeType = 'type0_Only';
IF
(
SELECT [ColumnType] FROM #types
) = 'type1'
SET @MergeType = 'type1_Only';
IF
(
SELECT [ColumnType] FROM #types
) = 'type2'
SET @MergeType = 'type2_Only';
END;
IF
(
SELECT COUNT(*)FROM #types
) = 2
BEGIN
IF
(
SELECT COUNT(*)
FROM #types t
INNER JOIN
(SELECT t2 = 'type0' UNION ALL SELECT 'type1') t01 ON t01.t2 = t.[ColumnType]
) = 2
SET @MergeType = 'type1_Only';
IF
(
SELECT COUNT(*)
FROM #types t
INNER JOIN
(SELECT t2 = 'type0' UNION ALL SELECT 'type2') t02 ON t02.t2 = t.[ColumnType]
) = 2
SET @MergeType = 'type2_Only';
IF
(
SELECT COUNT(*)
FROM #types t
INNER JOIN
(SELECT t2 = 'type1' UNION ALL SELECT 'type2') t12 ON t12.t2 = t.[ColumnType]
) = 2
SET @MergeType = 'Full';
END;
IF
(
SELECT COUNT(*)FROM #types
) = 3
SET @MergeType = 'Full';
/* throw message when all columns are key columns */
IF @MergeType IS NULL
BEGIN
SELECT @MergeType = [ColumnType]
FROM dbo.DimDataMap
WHERE DstSchema = @DstSchema
AND DstTable = @DstTable;
END;
IF @MergeType IN ('SK', 'NK', 'HK')
BEGIN
PRINT 'Holy ****! This isn''t good. But don''t freak out, i may be looking at wrong mappings'
+ 'or smoking something (you never know, heh).' + CHAR(10) + 'NO, WAIT... All columns for ' + @DstSchema
+ '.' + @DstTable + ' are defined as key (SK, NK, or HK) columns.'
+ 'Merge statement can''t be generated in this case.' + CHAR(10) + CHAR(10);
END;
IF @MergeType IN ('type0_only', 'type1_only', 'type2_only', 'Full')
BEGIN
/* Identify source schema and table */
SET @SrcSchema = @DstSchema;
-- SET @SrcTable = SUBSTRING(@DstTable, 4, LEN(@DstTable) - 3);
--Defining SorceTable as Destination Table + Staging...This should be changed if you have different Destination Table Names
SET @SrcTable = (@DstTable + 'Staging');
/* line break */
DECLARE @crlf CHAR(2) = CHAR(10);
/* Identify natural key(s) */
DECLARE curNK CURSOR FOR
SELECT DstColumn
, SrcColumn
FROM dbo.DimDataMap m
WHERE ColumnType = 'NK'
AND DstSchema = @DstSchema
AND DstTable = @DstTable
ORDER BY SortID;
/* Identify type1 fields */
DECLARE curType1 CURSOR FOR
SELECT DstColumn
, NullDstColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
, SrcColumn
, NullSrcColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
FROM dbo.DimDataMap m
INNER JOIN sys.schemas s ON s.name = m.DstSchema
INNER JOIN sys.tables t ON t.name = m.DstTable
INNER JOIN sys.columns c ON c.name = m.DstColumn
AND c.object_id = t.object_id
INNER JOIN sys.types ct ON ct.user_type_id = c.user_type_id
WHERE ColumnType = 'type1'
AND DstSchema = @DstSchema
AND DstTable = @DstTable
ORDER BY SortID;
/* Identify type2 fields */
DECLARE curType2 CURSOR FOR
SELECT DstColumn
, NullDstColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
, SrcColumn
, NullSrcColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
FROM dbo.DimDataMap m
INNER JOIN sys.schemas s ON s.name = m.DstSchema
INNER JOIN sys.tables t ON t.name = m.DstTable
INNER JOIN sys.columns c ON c.name = m.DstColumn
AND c.object_id = t.object_id
INNER JOIN sys.types ct ON ct.user_type_id = c.user_type_id
WHERE ColumnType = 'type2'
AND DstSchema = @DstSchema
AND DstTable = @DstTable
ORDER BY SortID;
/* identify all fields exclude surrogate and housekeeping keys (SK, HK) */
DECLARE curAll CURSOR FOR
SELECT DstColumn
, NullDstColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
, SrcColumn = ISNULL(SrcColumn, DstColumn)
, NullSrcColumn = CASE
WHEN ct.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
WHEN ct.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit'
, 'decimal', 'numeric', 'smallmoney', 'bigint'
) THEN '0'
WHEN ct.name IN ('date', 'datetime') THEN '''19000101'''
END
FROM dbo.DimDataMap m
INNER JOIN sys.schemas s ON s.name = m.DstSchema
INNER JOIN sys.tables t ON t.name = m.DstTable
INNER JOIN sys.columns c ON c.name = m.DstColumn
AND c.object_id = t.object_id
INNER JOIN sys.types ct ON ct.user_type_id = c.user_type_id
WHERE DstSchema = @DstSchema
AND DstTable = @DstTable
AND m.ColumnType NOT IN ('SK', 'HK')
ORDER BY SortID;
/* start building sql */
DECLARE @sql NVARCHAR(MAX)
, @sql2 NVARCHAR(MAX)
, @sql3 NVARCHAR(MAX)
, @SrcColumn VARCHAR(MAX)
, @NullSrcColumn VARCHAR(MAX)
, @DstColumn VARCHAR(MAX)
, @NullDstColumn VARCHAR(MAX);
/* if all columns in the dimension are type0, only insert new records*/
IF @MergeType = 'type0_only'
BEGIN
SET @sql = N'/*
Handling Type 0 Dimensions
*/' ;
SET @sql = @sql + @crlf
+ N'IF EXISTS ( SELECT *
FROM sys.procedures p
INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE p.name = ''Merge' + @DstTable + N'''' + N' AND s.name = ''dbo'' )
DROP PROC dbo.Load_Merge' + @DstTable + N';
GO ' ;
SET @sql = @sql + @crlf + @crlf + N'CREATE PROCEDURE dbo.Merge' + @DstTable + @crlf;
SET @sql = @sql + N' /* Add your preferred comment header here */' + @crlf;
SET @sql = @sql + N'AS' + @crlf;
SET @sql = @sql + N'BEGIN' + @crlf;
SET @sql = @sql + N'SET NOCOUNT ON ;' + @crlf;
SET @sql = @sql + N'MERGE ' + @DstSchema + N'.' + @DstTable + N' trg' + @crlf;
SET @sql = @sql + N'USING ' + @SrcSchema + N'.' + @SrcTable + N' src' + @crlf;
OPEN curNK;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' ON trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + N' AND trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
CLOSE curNK;
SET @sql = @sql + @crlf + N' WHEN NOT MATCHED BY TARGET' + @crlf;
SET @sql = @sql + N' THEN' + @crlf;
SET @sql = @sql + N' INSERT (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql = @sql + @crlf + N' )' + @crlf;
SET @sql = @sql + N' VALUES (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
/* kill cursors*/
DEALLOCATE curNK;
DEALLOCATE curType1;
DEALLOCATE curType2;
DEALLOCATE curAll;
SET @sql = @sql + @crlf + N' ) ; ' + @crlf;
SET @sql = @sql + N'END';
/* print sql output*/
PRINT @sql;
END;
/* if all columns in the dimension are type1, insert new records and update (type1) existing rows*/
IF @MergeType = 'type1_only'
BEGIN
SET @sql = N'/*
/*=======================================================================================================================================
-- Author :Mihir Shah
-- Created :04/07/2018
-- Purpose/Description
-- This query will load incremental Load
*/
*/' ;
SET @sql = @sql + @crlf
+ N'IF EXISTS ( SELECT *
FROM sys.procedures p
INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE p.name = ''Load_Merge' + @DstTable + N''''
+ N' AND s.name = ''dbo'' )
DROP PROC dbo.Load_MergeDimCustomer;
GO ' ;
SET @sql = @sql + @crlf + @crlf + N'CREATE PROCEDURE dbo.Load_Merge' + @DstTable + @crlf;
SET @sql = @sql + @crlf;
SET @sql = @sql + N'AS' + @crlf;
SET @sql = @sql + N'BEGIN' + @crlf;
SET @sql = @sql + N'SET NOCOUNT ON ;' + @crlf;
SET @sql = @sql + N'MERGE ' + @DstSchema + N'.' + @DstTable + N' trg' + @crlf;
SET @sql = @sql + N'USING ' + @SrcSchema + N'.' + @SrcTable + N' src' + @crlf;
OPEN curNK;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' ON trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + N' AND trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
CLOSE curNK;
SET @sql = @sql + @crlf + N' WHEN NOT MATCHED BY TARGET' + @crlf;
SET @sql = @sql + N' THEN' + @crlf;
SET @sql = @sql + N' INSERT (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql = @sql + @crlf + N' )' + @crlf;
SET @sql = @sql + N' VALUES (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql = @sql + @crlf + N' )' + @crlf;
SET @sql = @sql + N' WHEN MATCHED' + @crlf;
SET @sql = @sql + N' AND EXISTS (SELECT' + @crlf;
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql = @sql + @crlf + N' EXCEPT SELECT ';
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' trg.' + @DstColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql2 = N' ) THEN' + @crlf;
SET @sql2 = @sql2 + N' UPDATE SET';
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql2 = @sql2 + @crlf + N' trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql2 = @sql2 + @crlf + N' , trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql2 = @sql2 + N' ;' + @crlf + @crlf;
SET @sql2 = @sql2 + N'END';
/* kill cursors */
DEALLOCATE curNK;
DEALLOCATE curType1;
DEALLOCATE curType2;
DEALLOCATE curAll;
/* print sql output */
PRINT @sql;
PRINT @sql2;
END;
/* if all columns in the dimension are type2, insert new records and update (type2) existing rows*/
/* HK columns are used when joining src and dest and inserting new records */
IF @MergeType = 'type2_Only'
BEGIN
SET @sql = N'/*
Handling Type 2 Dimensions
*/' ;
SET @sql = @sql + @crlf
+ N'IF EXISTS ( SELECT *
FROM sys.procedures p
INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE p.name = ''Load_Merge' + @DstTable + N''''
+ N' AND s.name = ''dbo'' )
DROP PROC dbo.Load_MergeDimCustomer;
GO ' ;
SET @sql = @sql + @crlf + @crlf + N'CREATE PROCEDURE dbo.Load_Merge' + @DstTable + @crlf;
SET @sql = @sql + N' /* Add your preferred comment header here */' + @crlf;
SET @sql = @sql + N'AS' + @crlf;
SET @sql = @sql + N'BEGIN' + @crlf;
SET @sql = @sql + N'SET NOCOUNT ON ;' + @crlf;
SET @sql2 = N'INSERT ' + @DstSchema + N'.' + @DstTable + N' (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql2 = @sql2 + N' trg.' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql2 = @sql2 + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql2 = @sql2 + N' , trg.RowStartDate' + @crlf;
SET @sql2 = @sql2 + N' , trg.RowEndDate' + @crlf;
SET @sql2 = @sql2 + N' , trg.IsRowActive' + @crlf;
SET @sql2 = @sql2 + N' )';
SET @sql3 = N'SELECT' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , ' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , IsRowActive' + @crlf;
SET @sql3 = @sql3 + N'FROM (' + @crlf;
SET @sql3 = @sql3 + N' MERGE ' + @DstSchema + N'.' + @DstTable + N' trg' + @crlf;
SET @sql3 = @sql3 + N' USING ' + @SrcSchema + N'.' + @SrcTable + N' src' + @crlf;
OPEN curNK;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ON trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' AND trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
CLOSE curNK;
SET @sql3 = @sql3 + @crlf + N' WHEN NOT MATCHED BY TARGET' + @crlf;
SET @sql3 = @sql3 + N' THEN' + @crlf;
SET @sql3 = @sql3 + N' INSERT (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , IsRowActive' + @crlf;
SET @sql3 = @sql3 + N' )' + @crlf;
SET @sql3 = @sql3 + N' VALUES (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , GETDATE()' + @crlf;
SET @sql3 = @sql3 + N' , NULL' + @crlf;
SET @sql3 = @sql3 + N' , 1' + @crlf;
SET @sql3 = @sql3 + N' )' + @crlf;
SET @sql3 = @sql3 + N' WHEN MATCHED' + @crlf;
SET @sql3 = @sql3 + N' AND IsRowActive = 1' + @crlf;
SET @sql3 = @sql3 + N' AND EXISTS (SELECT ' + @crlf;
OPEN curType2;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' src.' + @SrcColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType2;
SET @sql3 = @sql3 + @crlf + N' EXCEPT SELECT ';
OPEN curType2;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' trg.' + @DstColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType2;
SET @sql3 = @sql3 + @crlf + N' )' + @crlf;
SET @sql3 = @sql3 + N' THEN' + @crlf;
SET @sql3 = @sql3 + N' UPDATE' + @crlf;
SET @sql3 = @sql3 + N' SET IsRowActive = 0' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate = DATEADD(dd,-1,GETDATE())' + @crlf;
SET @sql3 = @sql3 + N' OUTPUT $ACTION Action_Out' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , GETDATE() RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , NULL RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , 1 IsRowActive' + @crlf;
SET @sql3 = @sql3 + N' )m' + @crlf;
SET @sql3 = @sql3 + N'WHERE m.Action_Out = ''UPDATE''' + @crlf;
SET @sql3 = @sql3 + N'END ;' + @crlf;
/* kills cursors */
DEALLOCATE curNK;
DEALLOCATE curType1;
DEALLOCATE curType2;
DEALLOCATE curAll;
/* print sql output */
PRINT @sql;
PRINT @sql2;
PRINT @sql3;
END;
/* if columns in the dimension are of different types (type1, type2), insert new records
and update existing (type1 or type2) records. Type0 are ignored.*/
IF @MergeType = 'Full'
BEGIN
SET @sql = N'/*
Handling Type1 and Type 2 dimensions
*/' ;
SET @sql = @sql + @crlf
+ N'IF EXISTS ( SELECT *
FROM sys.procedures p
INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE p.name = ''Load_Merge' + @DstTable + N''''
+ N' AND s.name = ''dbo'' )
DROP PROC dbo.Load_MergeDimCustomer;
GO ' ;
SET @sql = @sql + @crlf + @crlf + N'CREATE PROCEDURE dbo.Load_Merge' + @DstTable + @crlf;
SET @sql = @sql + N' /* Add your preferred comment header here */' + @crlf;
SET @sql = @sql + N'AS' + @crlf;
SET @sql = @sql + N'BEGIN' + @crlf;
SET @sql = @sql + N'SET NOCOUNT ON ;' + @crlf;
SET @sql = @sql + N'MERGE ' + @DstSchema + N'.' + @DstTable + N' trg' + @crlf;
SET @sql = @sql + N'USING ' + @SrcSchema + N'.' + @SrcTable + N' src' + @crlf;
OPEN curNK;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' ON trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + N' AND trg.' + @DstColumn + N' = src.' + @SrcColumn + @crlf;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
CLOSE curNK;
SET @sql = @sql + N' AND trg.IsRowActive = 1' + @crlf;
SET @sql = @sql + N'WHEN MATCHED' + @crlf;
SET @sql = @sql + N' AND EXISTS (SELECT' + @crlf;
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql = @sql + @crlf + N' EXCEPT SELECT ';
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql = @sql + N' trg.' + @DstColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql = @sql + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql = @sql + @crlf + N' )';
SET @sql2 = N' THEN' + @crlf;
SET @sql2 = @sql2 + N' UPDATE SET';
OPEN curType1;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql2 = @sql2 + @crlf + N' trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql2 = @sql2 + @crlf + N' , trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curType1
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType1;
SET @sql2 = @sql2 + N' ;' + @crlf + @crlf;
SET @sql2 = @sql2 + N'INSERT ' + @DstSchema + N'.' + @DstTable + N' (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql2 = @sql2 + N' trg.' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql2 = @sql2 + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql2 = @sql2 + N' , trg.RowStartDate' + @crlf;
SET @sql2 = @sql2 + N' , trg.RowEndDate' + @crlf;
SET @sql2 = @sql2 + N' , trg.IsRowActive' + @crlf;
SET @sql2 = @sql2 + N' )';
SET @sql3 = N'SELECT' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , ' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , IsRowActive' + @crlf;
SET @sql3 = @sql3 + N'FROM (' + @crlf;
SET @sql3 = @sql3 + N' MERGE ' + @DstSchema + N'.' + @DstTable + N' trg' + @crlf;
SET @sql3 = @sql3 + N' USING ' + @SrcSchema + N'.' + @SrcTable + N' src' + @crlf;
OPEN curNK;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ON trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' AND trg.' + @DstColumn + N' = src.' + @SrcColumn;
FETCH NEXT FROM curNK
INTO @DstColumn
, @SrcColumn;
END;
CLOSE curNK;
SET @sql3 = @sql3 + @crlf + N' WHEN NOT MATCHED BY TARGET' + @crlf;
SET @sql3 = @sql3 + N' THEN' + @crlf;
SET @sql3 = @sql3 + N' INSERT (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , ' + @DstColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , IsRowActive' + @crlf;
SET @sql3 = @sql3 + N' )' + @crlf;
SET @sql3 = @sql3 + N' VALUES (' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , GETDATE()' + @crlf;
SET @sql3 = @sql3 + N' , NULL' + @crlf;
SET @sql3 = @sql3 + N' , 1' + @crlf;
SET @sql3 = @sql3 + N' )' + @crlf;
SET @sql3 = @sql3 + N' WHEN MATCHED' + @crlf;
SET @sql3 = @sql3 + N' AND IsRowActive = 1' + @crlf;
/********************************************/
SET @sql3 = @sql3 + N' AND EXISTS (SELECT ' + @crlf;
OPEN curType2;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' src.' + @SrcColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType2;
SET @sql3 = @sql3 + @crlf + N' EXCEPT SELECT ';
OPEN curType2;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' trg.' + @DstColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , trg.' + @DstColumn;
FETCH NEXT FROM curType2
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curType2;
SET @sql3 = @sql3 + @crlf + N' )' + @crlf;
SET @sql3 = @sql3 + N' THEN' + @crlf;
SET @sql3 = @sql3 + N' UPDATE' + @crlf;
SET @sql3 = @sql3 + N' SET IsRowActive = 0' + @crlf;
SET @sql3 = @sql3 + N' , RowEndDate = DATEADD(dd,-1,GETDATE())' + @crlf;
SET @sql3 = @sql3 + N' OUTPUT $ACTION Action_Out' + @crlf;
OPEN curAll;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
IF (@@FETCH_STATUS >= 0)
BEGIN
SET @sql3 = @sql3 + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @sql3 = @sql3 + @crlf + N' , src.' + @SrcColumn;
FETCH NEXT FROM curAll
INTO @DstColumn
, @NullDstColumn
, @SrcColumn
, @NullSrcColumn;
END;
CLOSE curAll;
SET @sql3 = @sql3 + @crlf + N' , GETDATE() RowStartDate' + @crlf;
SET @sql3 = @sql3 + N' , NULL RowEndDate' + @crlf;
SET @sql3 = @sql3 + N' , 1 IsRowActive' + @crlf;
SET @sql3 = @sql3 + N' )m' + @crlf;
SET @sql3 = @sql3 + N'WHERE m.Action_Out = ''UPDATE''' + @crlf;
SET @sql3 = @sql3 + N'END ;' + @crlf;
/* kill cursors */
DEALLOCATE curNK;
DEALLOCATE curType1;
DEALLOCATE curType2;
DEALLOCATE curAll;
/* print sql output*/
PRINT @sql;
PRINT @sql2;
PRINT @sql3;
END;
END;
GO
Comments
Post a Comment