daily pastebin goal
42%
SHARE
TWEET

Untitled

a guest Nov 17th, 2017 63 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top