· 8 years ago · Nov 17, 2017, 12:54 PM
1CREATE PROCEDURE System.sp_generate_merge
2(
3 @table_name varchar(776), -- The table/view for which the MERGE statement will be generated using the existing data
4 @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted/updated/deleted
5 @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
6 @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the MERGE statement
7 @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
8 @schema varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
9 @ommit_images bit = 0, -- Use this parameter to generate MERGE statement by omitting the 'image' columns
10 @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
11 @top int = NULL, -- Use this parameter to generate a MERGE statement only for the TOP n rows
12 @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the MERGE statement
13 @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the MERGE statement
14 @update_only_if_changed bit = 1, -- When 1, only performs an UPDATE operation if an included column in a matched row has changed.
15 @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the MERGE statement
16 @ommit_computed_cols bit = 0, -- When 1, computed columns will not be included in the MERGE statement
17 @pk_constraint_type varchar(50) = 'PRIMARY KEY', -- CONSTRAINT_TYPE = 'UNIQUE' - use if @ommit_identity = 1
18 @fk_replace_ColumnList varchar(max) = NULL, -- @Delimiter = '|'
19 @fk_replace_ValuesList varchar(max) = NULL -- @Delimiter = '|'
20)
21AS
22BEGIN
23 -- ver1-0 2017-11-16
24
25/***********************************************************************************************************
26Example 1:
27 EXEC System.sp_generate_merge @schema = 'Export',
28 @table_name ='Dict',
29 @update_only_if_changed = 0,
30 @ommit_identity = 1,
31 @debug_mode = 1,
32 @pk_constraint_type = 'UNIQUE',
33 @fk_replace_ColumnList = 'DictType_ID|DataType_ID',
34 @fk_replace_ValuesList =
35' ''(Select sub.ID
36 From Export.DictType sub
37 Where sub.Code = '''''' + (Select sub.Code From Showcase.DictType sub Where sub.Id = main.[DictType_ID]) + ''''''
38 )''
39|
40 ''(Select sub.ID
41 From Export.DataType sub
42 Where sub.Code = '''''' + (Select sub.Code From XBRL.DataType sub Where sub.Id = main.[DataType_ID]) + ''''''
43 )''
44'
45
46***********************************************************************************************************/
47
48SET NOCOUNT ON
49
50
51--Making sure user only uses either @cols_to_include or @cols_to_exclude
52IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
53 BEGIN
54 RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
55 RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
56 END
57
58
59--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
60IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
61 BEGIN
62 RAISERROR('Invalid use of @cols_to_include property',16,1)
63 PRINT 'Specify column names surrounded by single quotes and separated by commas'
64 PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_include = "''title_id'',''title''"'
65 RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
66 END
67
68IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
69 BEGIN
70 RAISERROR('Invalid use of @cols_to_exclude property',16,1)
71 PRINT 'Specify column names surrounded by single quotes and separated by commas'
72 PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_exclude = "''title_id'',''title''"'
73 RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
74 END
75
76
77--Checking to see if the database name is specified along wih the table name
78--Your database context should be local to the table for which you want to generate a MERGE statement
79--specifying the database name is not allowed
80IF (PARSENAME(@table_name,3)) IS NOT NULL
81 BEGIN
82 RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
83 RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
84 END
85
86
87--Checking for the existence of 'user table' or 'view'
88--This procedure is not written to work on system tables
89--To script the data in system tables, just create a view on the system tables and script the view instead
90IF @schema IS NULL
91 BEGIN
92 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = SCHEMA_NAME())
93 BEGIN
94 RAISERROR('User table or view not found.',16,1)
95 PRINT 'You may see this error if the specified table is not in your default schema (' + SCHEMA_NAME() + '). In that case use @schema parameter to specify the schema name.'
96 PRINT 'Make sure you have SELECT permission on that table or view.'
97 RETURN -1 --Failure. Reason: There is no user table or view with this name
98 END
99 END
100ELSE
101 BEGIN
102 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @schema)
103 BEGIN
104 RAISERROR('User table or view not found.',16,1)
105 PRINT 'Make sure you have SELECT permission on that table or view.'
106 RETURN -1 --Failure. Reason: There is no user table or view with this name
107 END
108 END
109
110
111--Variable declarations
112DECLARE @Column_ID int,
113 @Column_List varchar(8000),
114 @Column_List_For_Update varchar(8000),
115 @Column_List_For_Check varchar(8000),
116 @Column_Name varchar(128),
117 @Column_Name_Unquoted varchar(128),
118 @Data_Type varchar(128),
119 @Actual_Values varchar(8000), --This is the string that will be finally executed to generate a MERGE statement
120 @IDN varchar(128), --Will contain the IDENTITY column's name in the table
121 @Target_Table_For_Output varchar(776),
122 @Source_Table_Qualified varchar(776),
123 @SqlValuesText varchar(Max),
124 @SqlMergeText varchar(Max),
125 @Delimiter char(1),
126 @fk_idx int
127
128
129CREATE TABLE #tmp_values
130 (val varchar(max))
131
132--Variable Initialization
133SET @IDN = ''
134SET @Column_ID = 0
135SET @Column_Name = ''
136SET @Column_Name_Unquoted = ''
137SET @Column_List = ''
138SET @Column_List_For_Update = ''
139SET @Column_List_For_Check = ''
140SET @Actual_Values = ''
141SET @SqlMergeText = ''
142SET @Delimiter = '|'
143
144--Variable Defaults
145IF @schema IS NULL
146 BEGIN
147 SET @Target_Table_For_Output = QUOTENAME(COALESCE(@target_table, @table_name))
148 END
149ELSE
150 BEGIN
151 SET @Target_Table_For_Output = QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name))
152 END
153
154SET @Source_Table_Qualified = QUOTENAME(COALESCE(@schema,SCHEMA_NAME())) + '.' + QUOTENAME(@table_name)
155
156--To get the first column's ID
157SELECT @Column_ID = MIN(ORDINAL_POSITION)
158FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
159WHERE TABLE_NAME = @table_name
160AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
161
162
163--Loop through all the columns of the table, to get the column names and their data types
164WHILE @Column_ID IS NOT NULL
165 BEGIN
166 SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
167 @Column_Name_Unquoted = COLUMN_NAME,
168 @Data_Type = DATA_TYPE
169 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
170 WHERE ORDINAL_POSITION = @Column_ID
171 AND TABLE_NAME = @table_name
172 AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
173
174 IF @cols_to_include IS NOT NULL --Selecting only user specified columns
175 BEGIN
176 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
177 BEGIN
178 GOTO SKIP_LOOP
179 END
180 END
181
182 IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
183 BEGIN
184 IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
185 BEGIN
186 GOTO SKIP_LOOP
187 END
188 END
189
190 --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
191 IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
192 BEGIN
193 IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
194 SET @IDN = @Column_Name
195 ELSE
196 GOTO SKIP_LOOP
197 END
198
199 --Making sure whether to output computed columns or not
200 IF @ommit_computed_cols = 1
201 BEGIN
202 IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
203 BEGIN
204 GOTO SKIP_LOOP
205 END
206 END
207
208 --Tables with columns of IMAGE data type are not supported for obvious reasons
209 IF(@Data_Type in ('image'))
210 BEGIN
211 IF (@ommit_images = 0)
212 BEGIN
213 RAISERROR('Tables with image columns are not supported.',16,1)
214 PRINT 'Use @ommit_images = 1 parameter to generate a MERGE for the rest of the columns.'
215 RETURN -1 --Failure. Reason: There is a column with image data type
216 END
217 ELSE
218 BEGIN
219 GOTO SKIP_LOOP
220 END
221 END
222
223
224 SET @fk_idx = 0
225
226 Select @fk_idx = [Index]
227 From System.SplitString(@fk_replace_ColumnList,@Delimiter)
228 Where Item = @Column_Name_Unquoted
229
230 --Determining the data type of the column and depending on the data type, the VALUES part of
231 --the MERGE statement is generated. Care is taken to handle columns with NULL values. Also
232 --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
233 SET @Actual_Values = @Actual_Values +
234 + Case when ISNULL(@fk_idx,0) > 0
235 then (Select top 1 [Item]
236 From System.SplitString(@fk_replace_ValuesList,@Delimiter)
237 Where [Index] = @fk_idx)
238 else CASE WHEN @Data_Type IN ('char','nchar')
239 THEN 'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
240 WHEN @Data_Type IN ('varchar','nvarchar')
241 THEN 'COALESCE('''''''' + REPLACE(' + @Column_Name + ','''''''','''''''''''')+'''''''',''NULL'')'
242 WHEN @Data_Type IN ('time','date','datetime','smalldatetime','datetime2')
243 THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',127))+'''''''',''NULL'')'
244 WHEN @Data_Type IN ('uniqueidentifier')
245 THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
246 WHEN @Data_Type IN ('text')
247 THEN 'COALESCE('''''''' + REPLACE(CONVERT(varchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
248 WHEN @Data_Type IN ('ntext')
249 THEN 'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
250 WHEN @Data_Type IN ('binary','varbinary')
251 THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
252 WHEN @Data_Type IN ('timestamp','rowversion')
253 THEN CASE WHEN @include_timestamp = 0
254 THEN '''DEFAULT'''
255 ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
256 END
257 WHEN @Data_Type IN ('float','real','money','smallmoney')
258 THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
259 ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
260 END
261 end
262 + '+' + ''',''' + ' + '
263
264 --Generating the column list for the MERGE statement
265 SET @Column_List = @Column_List + @Column_Name + ','
266
267 --Don't update Primary Key or Identity columns
268 IF NOT EXISTS(
269 SELECT 1
270 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
271 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
272 WHERE pk.TABLE_NAME = @table_name
273 AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
274 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
275 AND c.TABLE_NAME = pk.TABLE_NAME
276 AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
277 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
278 AND c.COLUMN_NAME = @Column_Name_Unquoted
279 )
280 BEGIN
281 SET @Column_List_For_Update = @Column_List_For_Update + @Column_Name + ' = Source.' + @Column_Name + ',
282'
283 SET @Column_List_For_Check = @Column_List_For_Check +
284 CASE @Data_Type
285 WHEN 'text' THEN 'CAST(Target.' + @Column_Name + ' AS varchar(max)) <> CAST(Source.' + @Column_Name + ' AS varchar(max)) OR '
286 WHEN 'ntext' THEN 'CAST(Target.' + @Column_Name + ' AS nvarchar(max)) <> CAST(Source.' + @Column_Name + ' AS nvarchar(max)) OR '
287 ELSE 'Target.' + @Column_Name + ' <> Source.' + @Column_Name + ' OR '
288 END
289 END
290
291 SKIP_LOOP: --The label used in GOTO
292
293 SELECT @Column_ID = MIN(ORDINAL_POSITION)
294 FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
295 WHERE TABLE_NAME = @table_name
296 AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
297 AND ORDINAL_POSITION > @Column_ID
298
299 END --Loop ends here!
300
301
302--To get rid of the extra characters that got concatenated during the last run through the loop
303IF LEN(@Column_List_For_Update) <> 0
304 BEGIN
305 SET @Column_List_For_Update = ' ' + LEFT(@Column_List_For_Update,len(@Column_List_For_Update) - 4)
306 END
307
308IF LEN(@Column_List_For_Check) <> 0
309 BEGIN
310 SET @Column_List_For_Check = LEFT(@Column_List_For_Check,len(@Column_List_For_Check) - 3)
311 END
312
313SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
314
315SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
316IF LEN(LTRIM(@Column_List)) = 0
317 BEGIN
318 RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
319 RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
320 END
321
322
323--Get the join columns ----------------------------------------------------------
324DECLARE @PK_column_list VARCHAR(8000)
325DECLARE @PK_column_joins VARCHAR(8000)
326SET @PK_column_list = ''
327SET @PK_column_joins = ''
328
329SELECT @PK_column_list = @PK_column_list + QUOTENAME(c.COLUMN_NAME) + ', '
330 ,@PK_column_joins = @PK_column_joins + 'Target.[' + c.COLUMN_NAME + '] = Source.[' + c.COLUMN_NAME + '] AND '
331FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
332INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
333WHERE pk.TABLE_NAME = @table_name
334AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
335AND CONSTRAINT_TYPE = @pk_constraint_type
336AND c.TABLE_NAME = pk.TABLE_NAME
337AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
338AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
339
340SET @PK_column_list = LEFT(@PK_column_list, LEN(@PK_column_list) -1)
341SET @PK_column_joins = LEFT(@PK_column_joins, LEN(@PK_column_joins) -4)
342
343
344--Forming the final string that will be executed, to output the a MERGE statement
345SET @Actual_Values =
346 'SELECT ' +
347 CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
348 '''' +
349 ' (''+ ' + @Actual_Values + '+'')''' + ' ' +
350 COALESCE(@from,' FROM ' + @Source_Table_Qualified + ' (NOLOCK) as main
351 ')
352
353
354--Determining whether to ouput any debug information
355IF @debug_mode =1
356 BEGIN
357 PRINT '/*****START OF DEBUG INFORMATION*****'
358 PRINT ''
359 PRINT 'The primary key column list:'
360 PRINT @PK_column_list
361 PRINT ''
362 PRINT 'The INSERT column list:'
363 PRINT @Column_List
364 PRINT ''
365 PRINT 'The UPDATE column list:'
366 PRINT @Column_List_For_Update
367 PRINT ''
368 PRINT 'The SELECT statement executed to generate the MERGE:'
369 PRINT @Actual_Values
370 PRINT ''
371 PRINT '*****END OF DEBUG INFORMATION*****/'
372 PRINT ''
373 END
374
375PRINT '--MERGE generated by ''sp_generate_merge'' stored procedure, Version 0.9'
376
377Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT ON'
378
379--Determining whether to print IDENTITY_INSERT or not
380IF (LEN(@IDN) <> 0)
381 BEGIN
382 Set @SqlMergeText = @SqlMergeText + char(13) + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' ON'
383 END
384
385
386--Temporarily disable constraints on the target table
387IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
388 BEGIN
389 Set @SqlMergeText = @SqlMergeText + char(13) + 'ALTER TABLE ' + @Target_Table_For_Output + ' NOCHECK CONSTRAINT ALL' --Code to disable constraints temporarily
390 END
391
392
393--Output the start of the MERGE statement, qualifying with the schema name only if the caller explicitly specified it
394Set @SqlMergeText = @SqlMergeText + char(13) + 'MERGE INTO ' + @Target_Table_For_Output + ' AS Target'
395Set @SqlMergeText = @SqlMergeText + char(13) + 'USING (VALUES'
396
397
398--All the hard work pays off here!!! You'll get your MERGE statement, when the next line executes!
399
400Insert into #tmp_values (val)
401 EXEC (@Actual_Values)
402
403Set @SqlValuesText = NULL
404
405Select @SqlValuesText = COALESCE(', ' + @SqlValuesText , ' ') + val + char(13)
406 From #tmp_values
407 WHERE rtrim(ltrim(val)) != ''
408
409
410--Output the columns to correspond with each of the values above--------------------
411Set @SqlMergeText = @SqlMergeText
412 + char(13)
413 + @SqlValuesText
414 + ') AS Source (' + @Column_List + ')'
415
416
417--Output the join columns ----------------------------------------------------------
418Set @SqlMergeText = @SqlMergeText + char(13) + 'ON (' + @PK_column_joins + ')'
419
420
421--When matched, perform an UPDATE on any metadata columns only (ie. not on PK)------
422IF LEN(@Column_List_For_Update) <> 0
423BEGIN
424 Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN MATCHED ' + CASE WHEN @update_only_if_changed = 1 THEN 'AND (' + @Column_List_For_Check + ') ' ELSE '' END + 'THEN'
425 Set @SqlMergeText = @SqlMergeText + char(13) + ' UPDATE SET'
426 Set @SqlMergeText = @SqlMergeText + char(13) + @Column_List_For_Update
427END
428
429
430--When NOT matched by target, perform an INSERT------------------------------------
431Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY TARGET THEN';
432Set @SqlMergeText = @SqlMergeText + char(13) + ' INSERT(' + @Column_List + ')'
433Set @SqlMergeText = @SqlMergeText + char(13) + ' VALUES(' + REPLACE(@Column_List, '[', 'Source.[') + ')'
434
435/*
436-- skip Delete
437--When NOT matched by source, DELETE the row
438Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY SOURCE THEN '
439Set @SqlMergeText = @SqlMergeText + char(13) + ' DELETE;'
440Set @SqlMergeText = @SqlMergeText + char(13) + ''
441Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
442*/
443
444SET @SqlMergeText = @SqlMergeText + ';' + char(13) + 'GO'
445
446
447/*
448-- skip errors
449--Display the number of affected rows to the user, or report if an error occurred---
450Set @SqlMergeText = @SqlMergeText + char(13) + 'DECLARE @mergeError int'
451Set @SqlMergeText = @SqlMergeText + char(13) + ' , @mergeCount int'
452Set @SqlMergeText = @SqlMergeText + char(13) + 'SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT'
453Set @SqlMergeText = @SqlMergeText + char(13) + 'IF @mergeError != 0'
454Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
455Set @SqlMergeText = @SqlMergeText + char(13) + ' Set @SqlMergeText = @SqlMergeText + char(13) + ''ERROR OCCURRED IN MERGE FOR ' + @Target_Table_For_Output + '. Rows affected: '' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected';
456Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
457Set @SqlMergeText = @SqlMergeText + char(13) + 'ELSE'
458Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
459Set @SqlMergeText = @SqlMergeText + char(13) + ' Set @SqlMergeText = @SqlMergeText + char(13) + ''' + @Target_Table_For_Output + ' rows affected by MERGE: '' + CAST(@mergeCount AS VARCHAR(100));';
460Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
461Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
462*/
463
464--Re-enable the previously disabled constraints-------------------------------------
465IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
466 BEGIN
467 Set @SqlMergeText = @SqlMergeText + char(13) + 'ALTER TABLE ' + @Target_Table_For_Output + ' CHECK CONSTRAINT ALL' --Code to enable the previously disabled constraints
468 END
469
470
471--Switch-off identity inserting------------------------------------------------------
472IF (LEN(@IDN) <> 0)
473 BEGIN
474 Set @SqlMergeText = @SqlMergeText + char(13) + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' OFF'
475 END
476
477
478Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT OFF'
479Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
480
481SET NOCOUNT OFF
482
483Select @SqlMergeText
484
485RETURN 0 --Success. We are done!
486END
487GO