An auto generating SQL Merge stored procedure for Type 2 SCD

This could shave off some time in generating SQL Merge statement by hand. This helpful stored proc. will auto generate your merge statement on tables that use Type 0,1, and 2 SCD.  The dim table just needs to be setup to use extended properties.

He uses the schema dim and etl to represent the dimension table and staging table, respectfully. The field IsRowCurrent is used to represent the current updated row.

http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics/
http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd/

Code Snippet

1. /***************************************************************
2. *                                                              *
3. *   Script for use with blog post                              *
4. *     "Automating T-SQL Merge to load Dimensions (SCD)"        *
5. *     http://www.purplefrogsystems.com/blog/2012/04/automating-t-sql-merge-to-load-dimensions-scd
6. *                                                              *   
7. *   Posted: 6th April 2012                                     *
8. *                                                              *
9. *   By: Alex Whittles - Purple Frog Business Intelligence      *
10. *       www.PurpleFrogSystems.com                              *
11. *                                                              *
12. *   All code samples are provided “AS IS” without warranty of  *
13. *   any kind, either express or implied, including but not     *
14. *   limited to the implied warranties of merchantability       *
15. *   and/or fitness for a particular purpose.                   *
16. *                                                              *
17. ***************************************************************/
18.  
19. --The generate merge proc
20. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateMerge]') AND type in (N'P', N'PC'))
21. DROP PROCEDURE [dbo].[GenerateMerge]
22. GO
23.  
24. CREATE PROCEDURE [dbo].[GenerateMerge]
25.     @Dimension  varchar(50),
26.     @Schema     varchar(50)='dim',
27.     @ETLSchema  varchar(50)='etl',
28.     @Execute    bit=0  --Should the resulting merge be returned or executed
29. AS
30. BEGIN
31.     SET NOCOUNT ON;
32.  
33.     --Create Carriage return variable to help format the resulting query
34.     DECLARE @crlf char(2)
35.     SET @crlf = CHAR(13)
36.  
37.  
38.     --Find out which Audit fields are used
39.     DECLARE @UseIsInferred bit
40.     DECLARE @UseValidFrom bit
41.     DECLARE @UseValidTo bit
42.     DECLARE @UseIsRowCurrent bit
43.     DECLARE @UseLastUpdated bit
44.     
45.     SET @UseIsInferred = ISNULL((SELECT MAX(1)
46.     FROM sys.columns c
47.             INNER JOIN sys.tables t on c.object_id = t.object_id
48.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
49.     WHERE       s.name = @Schema
50.             AND t.name = @Dimension
51.             AND c.name = 'IsInferred'
52.     ),0)
53.  
54.     SET @UseValidFrom= ISNULL((SELECT MAX(1)
55.     FROM sys.columns c
56.             INNER JOIN sys.tables t on c.object_id = t.object_id
57.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
58.     WHERE       s.name = @Schema
59.             AND t.name = @Dimension
60.             AND c.name = 'ValidFrom'
61.     ),0)
62.  
63.     SET @UseValidTo = ISNULL((SELECT MAX(1)
64.     FROM sys.columns c
65.             INNER JOIN sys.tables t on c.object_id = t.object_id
66.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
67.     WHERE       s.name = @Schema
68.             AND t.name = @Dimension
69.             AND c.name = 'ValidTo'
70.     ),0)
71.  
72.     SET @UseIsRowCurrent = ISNULL((SELECT MAX(1)
73.     FROM sys.columns c
74.             INNER JOIN sys.tables t on c.object_id = t.object_id
75.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
76.     WHERE       s.name = @Schema
77.             AND t.name = @Dimension
78.             AND c.name = 'IsRowCurrent'
79.     ),0)
80.  
81.     SET @UseLastUpdated = ISNULL((SELECT MAX(1)
82.     FROM sys.columns c
83.             INNER JOIN sys.tables t on c.object_id = t.object_id
84.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
85.     WHERE       s.name = @Schema
86.             AND t.name = @Dimension
87.             AND c.name = 'LastUpdated'
88.     ),0)
89.  
90.  
91.     --Identify the business key column(s)
92.     --Also define what the null replacement should be
93.     DECLARE myCurBK Cursor FOR
94.     SELECT c.name
95.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
96.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
97.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
98.             ELSE 'NULL' END AS NullRep
99.     FROM sys.columns c
100.         INNER JOIN sys.tables t on c.object_id = t.object_id
101.         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
102.         INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
103.         INNER JOIN sys.extended_properties ep
104.              ON t.object_id=ep.major_id
105.             AND c.column_id=ep.minor_id
106.             AND ep.class=1
107.             AND ep.name='SCD'
108.     WHERE s.name = @Schema
109.         AND t.name = @Dimension
110.         AND ep.value = 'BK'
111.     ORDER BY c.column_id
112.     
113.     --Identify all fields to be merged (Exclude Type 0)
114.     DECLARE myCurType1 Cursor
115.     FOR SELECT c.name
116.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
117.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
118.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
119.             ELSE 'NULL' END AS NullRep
120.         FROM sys.columns c
121.             INNER JOIN sys.tables t on c.object_id = t.object_id
122.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
123.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
124.             LEFT JOIN sys.extended_properties ep
125.                  ON t.object_id=ep.major_id
126.                 AND c.column_id=ep.minor_id
127.                 AND ep.class=1
128.                 AND ep.name='SCD'
129.         WHERE s.name = @Schema
130.             AND t.name = @Dimension
131.             AND c.is_identity=0
132.             AND ISNULL(ep.value,'1') NOT IN ('0', 'Audit', 'BK')
133.         ORDER BY c.column_id ASC
134.  
135.     --Identify all fields for insert
136.     DECLARE myCurAll Cursor
137.     FOR SELECT c.name
138.         , CASE WHEN ty.name IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN ''''''
139.             WHEN ty.name IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'decimal', 'numeric','smallmoney','bigint') THEN '0'
140.             WHEN ty.name IN ('date', 'datetime') THEN '''19000101'''
141.             ELSE 'NULL' END AS NullRep
142.         FROM sys.columns c
143.             INNER JOIN sys.tables t on c.object_id = t.object_id
144.             INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
145.             INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
146.         WHERE s.name = @Schema
147.             AND t.name = @Dimension
148.             AND c.name NOT IN ('LastUpdated', 'IsInferred', 'ValidFrom', 'ValidTo', 'IsRowCurrent')
149.             AND c.is_identity=0
150.         ORDER BY c.column_id ASC
151.  
152.     DECLARE @Field varchar(255)
153.     DECLARE @NullRep varchar(20)
154.     
155.     DECLARE @SQL varchar(max)
156.     DECLARE @SQL2 varchar(max)
157.     DECLARE @SQL3 varchar(max)
158.  
159.     --Now start building up the dynamic SQL
160.     
161.     SET @SQL =                'MERGE      [' + @Schema + '].[' + @Dimension + '] AS Target'
162.     SET @SQL = @SQL + @crlf + '    USING [' + @ETLSchema + '].[' + @Dimension + '] AS Source'
163.     
164.     OPEN myCurBK
165.     FETCH NEXT FROM myCurBK INTO @Field, @NullRep
166.     IF (@@FETCH_STATUS>=0)
167.         BEGIN
168.             SET @SQL = @SQL + @crlf + '       ON Target.' + @Field + ' = Source.' + @Field
169.             FETCH NEXT FROM myCurBK INTO @Field, @NullRep
170.         END
171.     WHILE (@@FETCH_STATUS<>-1)
172.     BEGIN
173.         IF (@@FETCH_STATUS<>-2)
174.             SET @SQL = @SQL + @crlf + '       AND Target.' + @Field + ' = Source.' + @Field
175.         FETCH NEXT FROM myCurBK INTO @Field, @NullRep
176.     END
177.     CLOSE myCurBK
178.  
179.     IF @UseIsRowCurrent>0   SET @SQL = @SQL + @crlf + '       AND Target.IsRowCurrent=1'
180.     
181.     SET @SQL = @SQL + @crlf + '    WHEN MATCHED'
182.     
183.     OPEN myCurType1
184.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
185.     
186.     IF (@@FETCH_STATUS>=0)
187.         BEGIN
188.             SET @SQL = @SQL + @crlf + '       AND (ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
189.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
190.         END
191.     WHILE (@@FETCH_STATUS<>-1)
192.     BEGIN
193.         IF (@@FETCH_STATUS<>-2)
194.             SET @SQL = @SQL + @crlf + '       OR ISNULL(Target.' + @Field + ',' + @NullRep + ') <> ISNULL(Source.' + @Field + ',' + @NullRep + ')'
195.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
196.     END
197.     CLOSE myCurType1
198.     
199.     SET @SQL = @SQL + @crlf + '      )'
200.     SET @SQL2 = '    THEN UPDATE SET'
201.  
202.     OPEN myCurType1
203.     FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
204.     IF (@@FETCH_STATUS>=0)
205.         BEGIN
206.             SET @SQL2 = @SQL2 + @crlf + '       ' + @Field + ' = Source.' + @Field
207.             FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
208.         END
209.     WHILE (@@FETCH_STATUS<>-1)
210.     BEGIN
211.         IF (@@FETCH_STATUS<>-2)
212.             SET @SQL2 = @SQL2 + @crlf + '      ,' + @Field + ' = Source.' + @Field
213.         FETCH NEXT FROM myCurType1 INTO @Field, @NullRep
214.     END
215.     CLOSE myCurType1
216.     
217.     IF @UseLastUpdated>0   SET @SQL2 = @SQL2 + @crlf + '      ,LastUpdated = GetDate()'
218.     
219.     SET @SQL3 = '    WHEN NOT MATCHED THEN'
220.     SET @SQL3 = @SQL3 + @crlf + '         INSERT ('
221.     
222.     OPEN myCurAll
223.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
224.     IF (@@FETCH_STATUS>=0)
225.         BEGIN
226.             SET @SQL3 = @SQL3 + @crlf + '           ' + @Field
227.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
228.         END
229.     WHILE (@@FETCH_STATUS<>-1)
230.     BEGIN
231.         IF (@@FETCH_STATUS<>-2)
232.             SET @SQL3 = @SQL3 + @crlf + '           ,' + @Field
233.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
234.     END
235.     CLOSE myCurAll
236.     
237.      
238.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,IsInferred'
239.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,ValidFrom'
240.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,ValidTo'
241.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,IsRowCurrent'
242.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,LastUpdated'
243.     SET @SQL3 = @SQL3 + @crlf + '         ) VALUES ('
244.     
245.     OPEN myCurAll
246.     FETCH NEXT FROM myCurAll INTO @Field, @NullRep
247.     IF (@@FETCH_STATUS>=0)
248.         BEGIN
249.             SET @SQL3 = @SQL3 + @crlf + '            Source.' + @Field
250.             FETCH NEXT FROM myCurAll INTO @Field, @NullRep
251.         END
252.     WHILE (@@FETCH_STATUS<>-1)
253.     BEGIN
254.         IF (@@FETCH_STATUS<>-2)
255.             SET @SQL3 = @SQL3 + @crlf + '           ,Source.' + @Field
256.         FETCH NEXT FROM myCurAll INTO @Field, @NullRep
257.     END
258.     CLOSE myCurAll    
259.     
260.     IF @UseIsInferred>0     SET @SQL3 = @SQL3 + @crlf + '           ,0'
261.     IF @UseValidFrom>0      SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
262.     IF @UseValidTo>0        SET @SQL3 = @SQL3 + @crlf + '           ,NULL'
263.     IF @UseIsRowCurrent>0   SET @SQL3 = @SQL3 + @crlf + '           ,1'
264.     IF @UseLastUpdated>0    SET @SQL3 = @SQL3 + @crlf + '           ,GetDate()'
265.     SET @SQL3 = @SQL3 + @crlf + '         );'
266.     
267.     --clean up
268.     DEALLOCATE myCurType1
269.     DEALLOCATE myCurAll
270.     DEALLOCATE myCurBK
271.     
272.     IF @Execute = 1
273.     BEGIN
274.         EXEC(@SQL + @SQL2 + @SQL3)           
275.     END
276.     ELSE
277.     BEGIN    
278.         PRINT @SQL
279.         PRINT @SQL2
280.         PRINT @SQL3
281.     END
282.     
283. END
284.  
285. GO

An example of the extended properties being set through sql statement:

Code Snippet

1. EXEC sys.sp_addextendedproperty @level2name=N'CustomerKey',  @value=N'PK' ,    
2.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
3.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
4. EXEC sys.sp_addextendedproperty @level2name=N'Email',        @value=N'BK' ,    
5.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
6.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
7. EXEC sys.sp_addextendedproperty @level2name=N'FirstName',    @value=N'1' ,     
8.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
9.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
10. EXEC sys.sp_addextendedproperty @level2name=N'LastName',     @value=N'1' ,     
11.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
12.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
13. EXEC sys.sp_addextendedproperty @level2name=N'DoB',          @value=N'1' ,     
14.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
15.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
16. EXEC sys.sp_addextendedproperty @level2name=N'Sex',          @value=N'1' ,     
17.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
18.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
19. EXEC sys.sp_addextendedproperty @level2name=N'MaritalStatus',@value=N'1' ,     
20.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
21.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
22. EXEC sys.sp_addextendedproperty @level2name=N'FirstCreated', @value=N'1' ,     
23.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
24.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
25. EXEC sys.sp_addextendedproperty @level2name=N'ValidFrom',    @value=N'Audit' ,
26.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
27.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
28. EXEC sys.sp_addextendedproperty @level2name=N'ValidTo',      @value=N'Audit' ,
29.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
30.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
31. EXEC sys.sp_addextendedproperty @level2name=N'IsRowCurrent', @value=N'Audit' ,
32.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
33.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'
34. EXEC sys.sp_addextendedproperty @level2name=N'LastUpdated',  @value=N'Audit' ,
35.     @name=N'SCD', @level0type=N'SCHEMA',@level0name=N'Dim',
36.     @level1type=N'TABLE',@level1name=N'Customer', @level2type=N'COLUMN'

An example of printing the script:

Code Snippet

1. EXEC dbo.GenerateMerge
2.     @Dimension = 'Customer',
3.     @Schema = 'dim',
4.     @ETLSchema = 'etl',
5.     @Execute = 0