daily pastebin goal
20%
SHARE
TWEET

Untitled

a guest Nov 17th, 2017 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE 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. )
  21. AS
  22. BEGIN
  23.     -- ver1-0   2017-11-16
  24.  
  25. /***********************************************************************************************************
  26. Example 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.  
  48. SET NOCOUNT ON
  49.  
  50.  
  51. --Making sure user only uses either @cols_to_include or @cols_to_exclude
  52. IF ((@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
  60. IF ((@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.  
  68. IF ((@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
  80. IF (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
  90. IF @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
  100. ELSE
  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
  112. DECLARE @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.  
  129. CREATE TABLE #tmp_values
  130.     (val varchar(max))
  131.  
  132. --Variable Initialization
  133. SET @IDN = ''
  134. SET @Column_ID = 0
  135. SET @Column_Name = ''
  136. SET @Column_Name_Unquoted = ''
  137. SET @Column_List = ''
  138. SET @Column_List_For_Update = ''
  139. SET @Column_List_For_Check = ''
  140. SET @Actual_Values = ''
  141. SET @SqlMergeText = ''
  142. SET @Delimiter = '|'
  143.  
  144. --Variable Defaults
  145. IF @schema IS NULL
  146.  BEGIN
  147.  SET @Target_Table_For_Output = QUOTENAME(COALESCE(@target_table, @table_name))
  148.  END
  149. ELSE
  150.  BEGIN
  151.  SET @Target_Table_For_Output = QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name))
  152.  END
  153.  
  154. SET @Source_Table_Qualified = QUOTENAME(COALESCE(@schema,SCHEMA_NAME())) + '.' + QUOTENAME(@table_name)
  155.  
  156. --To get the first column's ID
  157. SELECT @Column_ID = MIN(ORDINAL_POSITION)
  158. FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
  159. WHERE TABLE_NAME = @table_name
  160. AND 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
  164. WHILE @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
  303. IF 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.  
  308. IF 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.  
  313. SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
  314.  
  315. SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
  316. IF 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 ----------------------------------------------------------
  324. DECLARE @PK_column_list VARCHAR(8000)
  325. DECLARE @PK_column_joins VARCHAR(8000)
  326. SET @PK_column_list = ''
  327. SET @PK_column_joins = ''
  328.  
  329. SELECT @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 '
  331. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
  332. INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  333. WHERE pk.TABLE_NAME = @table_name
  334. AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
  335. AND CONSTRAINT_TYPE = @pk_constraint_type
  336. AND c.TABLE_NAME = pk.TABLE_NAME
  337. AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
  338. AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  339.  
  340. SET @PK_column_list = LEFT(@PK_column_list, LEN(@PK_column_list) -1)
  341. SET @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
  345. SET @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
  355. IF @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.  
  375. PRINT '--MERGE generated by ''sp_generate_merge'' stored procedure, Version 0.9'
  376.  
  377. Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT ON'
  378.  
  379. --Determining whether to print IDENTITY_INSERT or not
  380. IF (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
  387. IF @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
  394. Set @SqlMergeText = @SqlMergeText + char(13) + 'MERGE INTO ' + @Target_Table_For_Output + ' AS Target'
  395. Set @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.  
  400. Insert into #tmp_values (val)
  401.     EXEC (@Actual_Values)
  402.  
  403. Set @SqlValuesText = NULL
  404.  
  405. Select @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--------------------
  411. Set @SqlMergeText = @SqlMergeText
  412.                     + char(13)
  413.                     + @SqlValuesText
  414.                     + ') AS Source (' + @Column_List + ')'
  415.  
  416.  
  417. --Output the join columns ----------------------------------------------------------
  418. Set @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)------
  422. IF LEN(@Column_List_For_Update) <> 0
  423. BEGIN
  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
  427. END
  428.  
  429.  
  430. --When NOT matched by target, perform an INSERT------------------------------------
  431. Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY TARGET THEN';
  432. Set @SqlMergeText = @SqlMergeText + char(13) + ' INSERT(' + @Column_List + ')'
  433. Set @SqlMergeText = @SqlMergeText + char(13) + ' VALUES(' + REPLACE(@Column_List, '[', 'Source.[') + ')'
  434.  
  435. /*
  436. -- skip Delete
  437. --When NOT matched by source, DELETE the row
  438. Set @SqlMergeText = @SqlMergeText + char(13) + 'WHEN NOT MATCHED BY SOURCE THEN '
  439. Set @SqlMergeText = @SqlMergeText + char(13) + ' DELETE;'
  440. Set @SqlMergeText = @SqlMergeText + char(13) + ''
  441. Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
  442. */
  443.  
  444. SET @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---
  450. Set @SqlMergeText = @SqlMergeText + char(13) + 'DECLARE @mergeError int'
  451. Set @SqlMergeText = @SqlMergeText + char(13) + ' , @mergeCount int'
  452. Set @SqlMergeText = @SqlMergeText + char(13) + 'SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT'
  453. Set @SqlMergeText = @SqlMergeText + char(13) + 'IF @mergeError != 0'
  454. Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
  455. Set @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';
  456. Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
  457. Set @SqlMergeText = @SqlMergeText + char(13) + 'ELSE'
  458. Set @SqlMergeText = @SqlMergeText + char(13) + ' BEGIN'
  459. Set @SqlMergeText = @SqlMergeText + char(13) + ' Set @SqlMergeText = @SqlMergeText + char(13) + ''' + @Target_Table_For_Output + ' rows affected by MERGE: '' + CAST(@mergeCount AS VARCHAR(100));';
  460. Set @SqlMergeText = @SqlMergeText + char(13) + ' END'
  461. Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
  462. */
  463.  
  464. --Re-enable the previously disabled constraints-------------------------------------
  465. IF @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------------------------------------------------------
  472. IF (LEN(@IDN) <> 0)
  473.  BEGIN
  474.  Set @SqlMergeText = @SqlMergeText + char(13) + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' OFF'
  475.  END
  476.  
  477.  
  478. Set @SqlMergeText = @SqlMergeText + char(13) + 'SET NOCOUNT OFF'
  479. Set @SqlMergeText = @SqlMergeText + char(13) + 'GO'
  480.  
  481. SET NOCOUNT OFF
  482.  
  483. Select @SqlMergeText
  484.  
  485. RETURN 0 --Success. We are done!
  486. END
  487. GO
RAW Paste Data
Top