Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET NOCOUNT ON
- GO
- PRINT 'Using Master database'
- USE master
- GO
- PRINT 'Checking for the existence of this procedure'
- IF (SELECT OBJECT_ID('sp_generate_merge','P')) IS NOT NULL --means, the procedure already exists
- BEGIN
- PRINT 'Procedure already exists. So, dropping it'
- DROP PROC sp_generate_merge
- END
- GO
- --Turn system object marking on
- CREATE PROC [sp_generate_merge]
- (
- @table_name varchar(776), -- The table/view for which the MERGE statement will be generated using the existing data
- @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted/updated/deleted
- @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
- @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the MERGE statement
- @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
- @schema varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
- @ommit_images bit = 0, -- Use this parameter to generate MERGE statement by omitting the 'image' columns
- @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
- @top int = NULL, -- Use this parameter to generate a MERGE statement only for the TOP n rows
- @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the MERGE statement
- @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the MERGE statement
- @update_only_if_changed bit = 1, -- When 1, only performs an UPDATE operation if an included column in a matched row has changed.
- @delete_if_not_matched bit = 1, -- When 1, deletes unmatched source rows from target, when 0 source rows will only be used to update existing rows or insert new.
- @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the MERGE statement
- @ommit_computed_cols bit = 0, -- When 1, computed columns will not be included in the MERGE statement
- @include_use_db bit = 1, -- When 1, includes a USE [DatabaseName] statement at the beginning of the generated batch
- @results_to_text bit = 0, -- When 1, outputs results to grid/messages window. When 0, outputs MERGE statement in an XML fragment.
- @include_rowsaffected bit = 1, -- When 1, a section is added to the end of the batch which outputs rows affected by the MERGE
- @nologo bit = 0, -- When 1, the "About" comment is suppressed from output
- @batch_separator VARCHAR(50) = 'GO' -- Batch separator to use
- )
- AS
- BEGIN
- /***********************************************************************************************************
- Procedure: sp_generate_merge (Version 0.93)
- (Adapted by Daniel Nolan for SQL Server 2008/2012)
- Adapted from: sp_generate_inserts (Build 22)
- (Copyright Β© 2002 Narayana Vyas Kondreddi. All rights reserved.)
- Purpose: To generate a MERGE statement from existing data, which will INSERT/UPDATE/DELETE data based
- on matching primary key values in the source/target table.
- The generated statements can be executed to replicate the data in some other location.
- Typical use cases:
- * Generate statements for static data tables, store the .SQL file in source control and use
- it as part of your Dev/Test/Prod deployment. The generated statements are re-runnable, so
- you can make changes to the file and migrate those changes between environments.
- * Generate statements from your Production tables and then run those statements in your
- Dev/Test environments. Schedule this as part of a SQL Job to keep all of your environments
- in-sync.
- * Enter test data into your Dev environment, and then generate statements from the Dev
- tables so that you can always reproduce your test database with valid sample data.
- Written by: Narayana Vyas Kondreddi
- http://vyaskn.tripod.com
- Daniel Nolan
- http://danere.com
- @dan3r3
- Acknowledgements (sp_generate_merge):
- Nathan Skerl -- StackOverflow answer that provided a workaround for the output truncation problem
- http://stackoverflow.com/a/10489767/266882
- Bill Gibson -- Blog that detailed the static data table use case; the inspiration for this proc
- http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx
- Bill Graziano -- Blog that provided the groundwork for MERGE statement generation
- http://weblogs.sqlteam.com/billg/archive/2011/02/15/generate-merge-statements-from-a-table.aspx
- Acknowledgements (sp_generate_inserts):
- Divya Kalra -- For beta testing
- Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values
- Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables
- Joris Laperre -- For reporting a regression bug in handling text/ntext columns
- Tested on: SQL Server 2008 (10.50.1600), SQL Server 2012 (11.0.2100)
- Date created: January 17th 2001 21:52 GMT
- Modified: May 1st 2002 19:50 GMT
- Last Modified: September 27th 2012 10:00 AEDT
- Email: dan@danere.com, vyaskn@hotmail.com
- NOTE: This procedure may not work with tables with a large number of columns (> 500).
- Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
- IMPORTANT: This procedure has not been extensively tested with international data (Extended characters or Unicode). If needed
- you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
- like nchar and nvarchar
- Get Started: Ensure that your SQL client is configured to send results to grid (default SSMS behaviour).
- This ensures that the generated MERGE statement can be output in full, getting around SSMS's 4000 nchar limit.
- After running this proc, click the hyperlink within the single row returned to copy the generated MERGE statement.
- Example 1: To generate a MERGE statement for table 'titles':
- EXEC sp_generate_merge 'titles'
- Example 2: To generate a MERGE statement for 'titlesCopy' table from 'titles' table:
- EXEC sp_generate_merge 'titles', 'titlesCopy'
- Example 3: To generate a MERGE statement for table 'titles' that will unconditionally UPDATE matching rows
- (ie. not perform a "has data changed?" check prior to going ahead with an UPDATE):
- EXEC sp_generate_merge 'titles', @update_only_if_changed = 0
- Example 4: To generate a MERGE statement for 'titles' table for only those titles
- which contain the word 'Computer' in them:
- NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
- EXEC sp_generate_merge 'titles', @from = "from titles where title like '%Computer%'"
- Example 5: To specify that you want to include TIMESTAMP column's data as well in the MERGE statement:
- (By default TIMESTAMP column's data is not scripted)
- EXEC sp_generate_merge 'titles', @include_timestamp = 1
- Example 6: To print the debug information:
- EXEC sp_generate_merge 'titles', @debug_mode = 1
- Example 7: If the table is in a different schema to the default, use @schema parameter to specify the schema name
- To use this option, you must have SELECT permissions on that table
- EXEC sp_generate_merge 'Nickstable', @schema = 'Nick'
- Example 8: To generate a MERGE statement for the rest of the columns excluding images
- EXEC sp_generate_merge 'imgtable', @ommit_images = 1
- Example 9: To generate a MERGE statement excluding (omitting) IDENTITY columns:
- (By default IDENTITY columns are included in the MERGE statement)
- EXEC sp_generate_merge 'mytable', @ommit_identity = 1
- Example 10: To generate a MERGE statement for the TOP 10 rows in the table:
- EXEC sp_generate_merge 'mytable', @top = 10
- Example 11: To generate a MERGE statement with only those columns you want:
- EXEC sp_generate_merge 'titles', @cols_to_include = "'title','title_id','au_id'"
- Example 12: To generate a MERGE statement by omitting certain columns:
- EXEC sp_generate_merge 'titles', @cols_to_exclude = "'title','title_id','au_id'"
- Example 13: To avoid checking the foreign key constraints while loading data with a MERGE statement:
- EXEC sp_generate_merge 'titles', @disable_constraints = 1
- Example 14: To exclude computed columns from the MERGE statement:
- EXEC sp_generate_merge 'MyTable', @ommit_computed_cols = 1
- ***********************************************************************************************************/
- SET NOCOUNT ON
- --Making sure user only uses either @cols_to_include or @cols_to_exclude
- IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
- BEGIN
- RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
- RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
- END
- --Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
- IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
- BEGIN
- RAISERROR('Invalid use of @cols_to_include property',16,1)
- PRINT 'Specify column names surrounded by single quotes and separated by commas'
- PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_include = "''title_id'',''title''"'
- RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
- END
- IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
- BEGIN
- RAISERROR('Invalid use of @cols_to_exclude property',16,1)
- PRINT 'Specify column names surrounded by single quotes and separated by commas'
- PRINT 'Eg: EXEC sp_generate_merge titles, @cols_to_exclude = "''title_id'',''title''"'
- RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
- END
- --Checking to see if the database name is specified along wih the table name
- --Your database context should be local to the table for which you want to generate a MERGE statement
- --specifying the database name is not allowed
- IF (PARSENAME(@table_name,3)) IS NOT NULL
- BEGIN
- RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
- RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
- END
- --Checking for the existence of 'user table' or 'view'
- --This procedure is not written to work on system tables
- --To script the data in system tables, just create a view on the system tables and script the view instead
- IF @schema IS NULL
- BEGIN
- 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())
- BEGIN
- RAISERROR('User table or view not found.',16,1)
- 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.'
- PRINT 'Make sure you have SELECT permission on that table or view.'
- RETURN -1 --Failure. Reason: There is no user table or view with this name
- END
- END
- ELSE
- BEGIN
- 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)
- BEGIN
- RAISERROR('User table or view not found.',16,1)
- PRINT 'Make sure you have SELECT permission on that table or view.'
- RETURN -1 --Failure. Reason: There is no user table or view with this name
- END
- END
- --Variable declarations
- DECLARE @Column_ID int,
- @Column_List varchar(8000),
- @Column_List_For_Update varchar(8000),
- @Column_List_For_Check varchar(8000),
- @Column_Name varchar(128),
- @Column_Name_Unquoted varchar(128),
- @Data_Type varchar(128),
- @Actual_Values nvarchar(max), --This is the string that will be finally executed to generate a MERGE statement
- @IDN varchar(128), --Will contain the IDENTITY column's name in the table
- @Target_Table_For_Output varchar(776),
- @Source_Table_Qualified varchar(776)
- --Variable Initialization
- SET @IDN = ''
- SET @Column_ID = 0
- SET @Column_Name = ''
- SET @Column_Name_Unquoted = ''
- SET @Column_List = ''
- SET @Column_List_For_Update = ''
- SET @Column_List_For_Check = ''
- SET @Actual_Values = ''
- --Variable Defaults
- IF @schema IS NULL
- BEGIN
- SET @Target_Table_For_Output = QUOTENAME(COALESCE(@target_table, @table_name))
- END
- ELSE
- BEGIN
- SET @Target_Table_For_Output = QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name))
- END
- SET @Source_Table_Qualified = QUOTENAME(COALESCE(@schema,SCHEMA_NAME())) + '.' + QUOTENAME(@table_name)
- --To get the first column's ID
- SELECT @Column_ID = MIN(ORDINAL_POSITION)
- FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
- WHERE TABLE_NAME = @table_name
- AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
- --Loop through all the columns of the table, to get the column names and their data types
- WHILE @Column_ID IS NOT NULL
- BEGIN
- SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
- @Column_Name_Unquoted = COLUMN_NAME,
- @Data_Type = DATA_TYPE
- FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
- WHERE ORDINAL_POSITION = @Column_ID
- AND TABLE_NAME = @table_name
- AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
- IF @cols_to_include IS NOT NULL --Selecting only user specified columns
- BEGIN
- IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
- BEGIN
- GOTO SKIP_LOOP
- END
- END
- IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
- BEGIN
- IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
- BEGIN
- GOTO SKIP_LOOP
- END
- END
- --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
- IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
- BEGIN
- IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
- SET @IDN = @Column_Name
- ELSE
- GOTO SKIP_LOOP
- END
- --Making sure whether to output computed columns or not
- IF @ommit_computed_cols = 1
- BEGIN
- IF (SELECT COLUMNPROPERTY( OBJECT_ID(@Source_Table_Qualified),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
- BEGIN
- GOTO SKIP_LOOP
- END
- END
- --Tables with columns of IMAGE data type are not supported for obvious reasons
- IF(@Data_Type in ('image'))
- BEGIN
- IF (@ommit_images = 0)
- BEGIN
- RAISERROR('Tables with image columns are not supported.',16,1)
- PRINT 'Use @ommit_images = 1 parameter to generate a MERGE for the rest of the columns.'
- RETURN -1 --Failure. Reason: There is a column with image data type
- END
- ELSE
- BEGIN
- GOTO SKIP_LOOP
- END
- END
- --Determining the data type of the column and depending on the data type, the VALUES part of
- --the MERGE statement is generated. Care is taken to handle columns with NULL values. Also
- --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
- SET @Actual_Values = @Actual_Values +
- CASE
- WHEN @Data_Type IN ('char','nchar')
- THEN
- 'COALESCE(''N'''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('varchar','nvarchar')
- THEN
- 'COALESCE(''N'''''' + REPLACE(' + @Column_Name + ','''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('datetime','smalldatetime','datetime2','date')
- THEN
- 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',127))+'''''''',''NULL'')'
- WHEN @Data_Type IN ('uniqueidentifier')
- THEN
- 'COALESCE(''N'''''' + REPLACE(CONVERT(char(36),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('text')
- THEN
- 'COALESCE(''N'''''' + REPLACE(CONVERT(varchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('ntext')
- THEN
- 'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('xml')
- THEN
- 'COALESCE('''''''' + REPLACE(CONVERT(nvarchar(max),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
- WHEN @Data_Type IN ('binary','varbinary')
- THEN
- 'COALESCE(RTRIM(CONVERT(varchar(max),' + @Column_Name + ', 1))),''NULL'')'
- WHEN @Data_Type IN ('timestamp','rowversion')
- THEN
- CASE
- WHEN @include_timestamp = 0
- THEN
- '''DEFAULT'''
- ELSE
- 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
- END
- WHEN @Data_Type IN ('float','real','money','smallmoney')
- THEN
- 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
- WHEN @Data_Type IN ('hierarchyid')
- THEN
- 'COALESCE(''hierarchyid::Parse(''+'''''''' + LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + '))+''''''''+'')'',''NULL'')'
- ELSE
- 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
- END + '+' + ''',''' + ' + '
- --Generating the column list for the MERGE statement
- SET @Column_List = @Column_List + @Column_Name + ','
- --Don't update Primary Key or Identity columns
- IF NOT EXISTS(
- SELECT 1
- FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
- WHERE pk.TABLE_NAME = @table_name
- AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
- AND CONSTRAINT_TYPE = 'PRIMARY KEY'
- AND c.TABLE_NAME = pk.TABLE_NAME
- AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
- AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
- AND c.COLUMN_NAME = @Column_Name_Unquoted
- )
- BEGIN
- SET @Column_List_For_Update = @Column_List_For_Update + @Column_Name + ' = Source.' + @Column_Name + ',
- '
- SET @Column_List_For_Check = @Column_List_For_Check +
- CASE @Data_Type
- WHEN 'text' THEN CHAR(10) + CHAR(9) + 'NULLIF(CAST(Source.' + @Column_Name + ' AS VARCHAR(MAX)), CAST(Target.' + @Column_Name + ' AS VARCHAR(MAX))) IS NOT NULL OR NULLIF(CAST(Target.' + @Column_Name + ' AS VARCHAR(MAX)), CAST(Source.' + @Column_Name + ' AS VARCHAR(MAX))) IS NOT NULL OR '
- WHEN 'ntext' THEN CHAR(10) + CHAR(9) + 'NULLIF(CAST(Source.' + @Column_Name + ' AS NVARCHAR(MAX)), CAST(Target.' + @Column_Name + ' AS NVARCHAR(MAX))) IS NOT NULL OR NULLIF(CAST(Target.' + @Column_Name + ' AS NVARCHAR(MAX)), CAST(Source.' + @Column_Name + ' AS NVARCHAR(MAX))) IS NOT NULL OR '
- ELSE CHAR(10) + CHAR(9) + 'NULLIF(Source.' + @Column_Name + ', Target.' + @Column_Name + ') IS NOT NULL OR NULLIF(Target.' + @Column_Name + ', Source.' + @Column_Name + ') IS NOT NULL OR '
- END
- END
- SKIP_LOOP: --The label used in GOTO
- SELECT @Column_ID = MIN(ORDINAL_POSITION)
- FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
- WHERE TABLE_NAME = @table_name
- AND TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
- AND ORDINAL_POSITION > @Column_ID
- END --Loop ends here!
- --To get rid of the extra characters that got concatenated during the last run through the loop
- IF LEN(@Column_List_For_Update) <> 0
- BEGIN
- SET @Column_List_For_Update = ' ' + LEFT(@Column_List_For_Update,len(@Column_List_For_Update) - 4)
- END
- IF LEN(@Column_List_For_Check) <> 0
- BEGIN
- SET @Column_List_For_Check = LEFT(@Column_List_For_Check,len(@Column_List_For_Check) - 3)
- END
- SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
- SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
- IF LEN(LTRIM(@Column_List)) = 0
- BEGIN
- RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
- RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
- END
- --Get the join columns ----------------------------------------------------------
- DECLARE @PK_column_list VARCHAR(8000)
- DECLARE @PK_column_joins VARCHAR(8000)
- SET @PK_column_list = ''
- SET @PK_column_joins = ''
- SELECT @PK_column_list = @PK_column_list + '[' + c.COLUMN_NAME + '], '
- , @PK_column_joins = @PK_column_joins + 'Target.[' + c.COLUMN_NAME + '] = Source.[' + c.COLUMN_NAME + '] AND '
- FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
- WHERE pk.TABLE_NAME = @table_name
- AND pk.TABLE_SCHEMA = COALESCE(@schema, SCHEMA_NAME())
- AND CONSTRAINT_TYPE = 'PRIMARY KEY'
- AND c.TABLE_NAME = pk.TABLE_NAME
- AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
- AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
- IF IsNull(@PK_column_list, '') = ''
- BEGIN
- RAISERROR('Table has no primary keys. There should at least be one column in order to have a valid join.',16,1)
- RETURN -1 --Failure. Reason: looks like table doesn't have any primary keys
- END
- SET @PK_column_list = LEFT(@PK_column_list, LEN(@PK_column_list) -1)
- SET @PK_column_joins = LEFT(@PK_column_joins, LEN(@PK_column_joins) -4)
- --Forming the final string that will be executed, to output the a MERGE statement
- SET @Actual_Values =
- 'SELECT ' +
- CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
- '''' +
- ' '' + CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + @PK_column_list + ') = 1 THEN '' '' ELSE '','' END + ''(''+ ' + @Actual_Values + '+'')''' + ' ' +
- COALESCE(@from,' FROM ' + @Source_Table_Qualified + ' (NOLOCK) ORDER BY ' + @PK_column_list)
- DECLARE @output VARCHAR(MAX) = ''
- DECLARE @b CHAR(1) = CHAR(13)
- --Determining whether to ouput any debug information
- IF @debug_mode =1
- BEGIN
- SET @output += @b + '/*****START OF DEBUG INFORMATION*****'
- SET @output += @b + ''
- SET @output += @b + 'The primary key column list:'
- SET @output += @b + @PK_column_list
- SET @output += @b + ''
- SET @output += @b + 'The INSERT column list:'
- SET @output += @b + @Column_List
- SET @output += @b + ''
- SET @output += @b + 'The UPDATE column list:'
- SET @output += @b + @Column_List_For_Update
- SET @output += @b + ''
- SET @output += @b + 'The SELECT statement executed to generate the MERGE:'
- SET @output += @b + @Actual_Values
- SET @output += @b + ''
- SET @output += @b + '*****END OF DEBUG INFORMATION*****/'
- SET @output += @b + ''
- END
- IF (@include_use_db = 1)
- BEGIN
- SET @output += 'USE ' + DB_NAME()
- SET @output += @b + @batch_separator
- SET @output += @b + @b
- END
- IF (@nologo = 0)
- BEGIN
- SET @output += @b + '--MERGE generated by ''sp_generate_merge'' stored procedure, Version 0.93'
- SET @output += @b + '--Originally by Vyas (http://vyaskn.tripod.com): sp_generate_inserts (build 22)'
- SET @output += @b + '--Adapted for SQL Server 2008/2012 by Daniel Nolan (http://danere.com)'
- SET @output += @b + ''
- END
- IF (@include_rowsaffected = 1) -- If the caller has elected not to include the "rows affected" section, let MERGE output the row count as it is executed.
- SET @output += @b + 'SET NOCOUNT ON'
- SET @output += @b + ''
- --Determining whether to print IDENTITY_INSERT or not
- IF (LEN(@IDN) <> 0)
- BEGIN
- SET @output += @b + 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' ON'
- SET @output += @b + ''
- END
- --Temporarily disable constraints on the target table
- IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
- BEGIN
- SET @output += @b + 'ALTER TABLE ' + @Target_Table_For_Output + ' NOCHECK CONSTRAINT ALL' --Code to disable constraints temporarily
- END
- --Output the start of the MERGE statement, qualifying with the schema name only if the caller explicitly specified it
- SET @output += @b + 'MERGE INTO ' + @Target_Table_For_Output + ' AS Target'
- SET @output += @b + 'USING (VALUES'
- --All the hard work pays off here!!! You'll get your MERGE statement, when the next line executes!
- DECLARE @tab TABLE (ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), val NVARCHAR(max));
- INSERT INTO @tab (val)
- EXEC (@Actual_Values)
- IF (SELECT COUNT(*) FROM @tab) <> 0 -- Ensure that rows were returned, otherwise the MERGE statement will get nullified.
- BEGIN
- SET @output += CAST((SELECT @b + val FROM @tab ORDER BY ID FOR XML PATH('')) AS XML).value('.', 'VARCHAR(MAX)');
- END
- --Output the columns to correspond with each of the values above--------------------
- SET @output += @b + ') AS Source (' + @Column_List + ')'
- --Output the join columns ----------------------------------------------------------
- SET @output += @b + 'ON (' + @PK_column_joins + ')'
- --When matched, perform an UPDATE on any metadata columns only (ie. not on PK)------
- IF LEN(@Column_List_For_Update) <> 0
- BEGIN
- SET @output += @b + 'WHEN MATCHED ' + CASE WHEN @update_only_if_changed = 1 THEN 'AND (' + @Column_List_For_Check + ') ' ELSE '' END + 'THEN'
- SET @output += @b + ' UPDATE SET'
- SET @output += @b + ' ' + LTRIM(@Column_List_For_Update)
- END
- --When NOT matched by target, perform an INSERT------------------------------------
- SET @output += @b + 'WHEN NOT MATCHED BY TARGET THEN';
- SET @output += @b + ' INSERT(' + @Column_List + ')'
- SET @output += @b + ' VALUES(' + REPLACE(@Column_List, '[', 'Source.[') + ')'
- --When NOT matched by source, DELETE the row
- IF @delete_if_not_matched=1 BEGIN
- SET @output += @b + 'WHEN NOT MATCHED BY SOURCE THEN '
- SET @output += @b + ' DELETE'
- END;
- SET @output += @b + ';'
- SET @output += @b + @batch_separator
- --Display the number of affected rows to the user, or report if an error occurred---
- IF @include_rowsaffected = 1
- BEGIN
- SET @output += @b + 'DECLARE @mergeError int'
- SET @output += @b + ' , @mergeCount int'
- SET @output += @b + 'SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT'
- SET @output += @b + 'IF @mergeError != 0'
- SET @output += @b + ' BEGIN'
- SET @output += @b + ' PRINT ''ERROR OCCURRED IN MERGE FOR ' + @Target_Table_For_Output + '. Rows affected: '' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected';
- SET @output += @b + ' END'
- SET @output += @b + 'ELSE'
- SET @output += @b + ' BEGIN'
- SET @output += @b + ' PRINT ''' + @Target_Table_For_Output + ' rows affected by MERGE: '' + CAST(@mergeCount AS VARCHAR(100));';
- SET @output += @b + ' END'
- SET @output += @b + @batch_separator
- SET @output += @b + @b
- END
- --Re-enable the previously disabled constraints-------------------------------------
- IF @disable_constraints = 1 AND (OBJECT_ID(@Source_Table_Qualified, 'U') IS NOT NULL)
- BEGIN
- SET @output += 'ALTER TABLE ' + @Target_Table_For_Output + ' CHECK CONSTRAINT ALL' --Code to enable the previously disabled constraints
- SET @output += @b + @batch_separator
- SET @output += @b
- END
- --Switch-off identity inserting------------------------------------------------------
- IF (LEN(@IDN) <> 0)
- BEGIN
- SET @output += 'SET IDENTITY_INSERT ' + @Target_Table_For_Output + ' OFF'
- SET @output += @b + @batch_separator
- SET @output += @b
- END
- IF (@include_rowsaffected = 1)
- BEGIN
- SET @output += 'SET NOCOUNT OFF'
- SET @output += @b + @batch_separator
- SET @output += @b
- END
- SET @output += @b + ''
- SET @output += @b + ''
- IF @results_to_text = 1
- BEGIN
- --output the statement to the Grid/Messages tab
- SELECT @output;
- END
- ELSE
- BEGIN
- --output the statement as xml (to overcome SSMS 4000/8000 char limitation)
- SELECT [processing-instruction(x)]=@output FOR XML PATH(''),TYPE;
- PRINT 'MERGE statement has been wrapped in an XML fragment and output successfully.'
- PRINT 'Ensure you have Results to Grid enabled and then click the hyperlink to copy the statement within the fragment.'
- PRINT ''
- PRINT 'If you would prefer to have results output directly (without XML) specify @results_to_text = 1, however please'
- PRINT 'note that the results may be truncated by your SQL client to 4000 nchars.'
- END
- SET NOCOUNT OFF
- RETURN 0 --Success. We are done!
- END
- GO
- PRINT 'Created the procedure'
- GO
- --Mark the proc as a system object to allow it to be called transparently from other databases
- EXEC sp_MS_marksystemobject sp_generate_merge
- GO
- PRINT 'Granting EXECUTE permission on sp_generate_merge to all users'
- GRANT EXEC ON sp_generate_merge TO public
- SET NOCOUNT OFF
- GO
- PRINT 'Done'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement