sample/mmp.dynamicDataLoad.sql at main · bch1212/sample · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
DROP PROC IF EXISTS mmp.dynamicDataLoad
GO

CREATE PROC mmp.dynamicDataLoad
(
     @etlMappingName VARCHAR(50)
    ,@sourceApplicationID INT
    ,@targetApplicationID INT
    ,@sourceTable VARCHAR(50)
    ,@targetTable VARCHAR(50)
    ,@etlCommand VARCHAR(MAX) = NULL
)

AS

SELECT DISTINCT @etlCommand = CONCAT
    (
        'INSERT INTO '
        ,targetSchema
        ,'.'
        ,targetTable
        ,'('
        ,
            (
                SELECT STUFF(
                (
                    SELECT ', ' + cast(targetColumn as varchar(max))
                    FROM mmp.etlMapping tm
                    WHERE tm.etlMappingName = @etlMappingName
                        AND tm.sourceApplicationID = @sourceApplicationID
                        AND tm.targetApplicationID = @targetApplicationID
                        AND tm.sourceTable = @sourceTable
                        AND tm.targetTable = @targetTable
                    ORDER BY sourceColumnOrder
                FOR XML PATH('')
                ), 1, 2, ''))
        ,')'
        ,' SELECT '
        ,
            (
                SELECT STUFF(
                (
                    SELECT ', ' + cast(sourceColumn as varchar(max))
                    FROM mmp.etlMapping sm
                    WHERE sm.etlMappingName = @etlMappingName
                        AND sm.sourceApplicationID = @sourceApplicationID
                        AND sm.targetApplicationID = @targetApplicationID
                        AND sm.sourceTable = @sourceTable
                        AND sm.targetTable = @targetTable
                    ORDER BY sourceColumnOrder
                FOR XML PATH('')
                ), 1, 2, '')
            )
        ,' FROM '
        ,sourceSchema
        ,'.'
        ,sourceTable
    )
FROM mmp.etlMapping m
WHERE m.etlMappingName = @etlMappingName
    AND m.sourceApplicationID = @sourceApplicationID
    AND m.targetApplicationID = @targetApplicationID
    AND m.sourceTable = @sourceTable
    AND m.targetTable = @targetTable

MERGE mmp.etlCommand AS tgt
USING
    (VALUES
        (
             'SQL'
            ,@etlCommand
            ,@etlMappingName
            ,@sourceApplicationID
            ,@targetApplicationID
            ,@sourceTable
            ,@targetTable
        )) AS src (etlCommandType, etlCommand, etlMappingName, sourceApplicationID, targetApplicationID, sourceTable, targetTable)
ON src.etlCommandType = tgt.etlCommandType
    AND src.etlMappingName = tgt.etlMappingName
    AND src.sourceApplicationID = tgt.sourceApplicationID
    AND src.targetApplicationID = tgt.targetApplicationID
    AND src.sourceTable = tgt.sourceTable
    AND src.targetTable = tgt.targetTable
WHEN MATCHED THEN
    UPDATE SET tgt.etlCommandText = src.etlCommand
WHEN NOT MATCHED THEN
    INSERT
    (
         etlCommandType
        ,etlCommandText
        ,etlMappingName
        ,sourceApplicationID
        ,targetApplicationID
        ,sourceTable
        ,targetTable
    )
    VALUES
    (
         src.etlCommandType
        ,src.etlCommand
        ,src.etlMappingName
        ,src.sourceApplicationID
        ,src.targetApplicationID
        ,src.sourceTable
        ,src.targetTable
    );

GO

-- EXEC mmp.dynamicDataLoad
--      @etlMappingName = 'Patient'
--     ,@sourceApplicationID = 2
--     ,@targetApplicationID = 2
--     ,@sourceTable = 'person'
--     ,@targetTable = 'dimPatient'