Create MERGE statements with data!

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Comments posted to this topic are about the item Create MERGE statements with data!

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    EDIT: In case this doesn't make it into the actual article, I wanted to clarify a few items.

    1. The script has a few restricting conditions that you may want to remove. Specifically, the check for “IsActive” or “Active” columns.

    2. The output, if copied and pasted into a new window should retain the proper formatting.

    3. Your table MUST have a Primary Key defined.

    4. Replacing the final select (that returns the statement) with the below code will handle >, < and & conversions caused by the use of XML datatype.

    BEGIN

    SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(@sqlTextFirst +

    STUFF((SELECT

    UsingList AS [text()]

    FROM

    #DataToMerge

    FOR XML PATH(''))

    ,1,1,'')+

    @sqlTextSecond

    ,'USING (VALUES ,(','USING (VALUES ('), '<', '<'), '>', '>'), '&', '&')

    END

    ** The USING clause should look like this.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    For those of you that would like a single procedure that doesn't depend upon the Extended Properties and will work for a single table.

    Here's what we use:

    /****** Object: StoredProcedure [Admin].[CreateMerge] Script Date: 9/3/2015 10:07:29 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    =============================================

    Author: Jason L. Selburg

    Create date: 07/25/2014

    Description: Returns the data for the program income receivables report

    - The 'Created','LastEdited','IsTiedToBL','TS' and GUID columns are ignored

    - @ExcludeData is set to 1, then only the merge statement is created with a single row with placeholders

    - @ReverseOrder and @TopCount are used when the data in the table exceeds the output length

    - @ReverseOrder orders the data by the PK DESC

    - @Topcount only return this number of rows. ONLY used when @ReverseOrder is set

    - @CustomWhere to hold a custom where claues for tables with large amounts of data

    */

    ALTER PROCEDURE [Admin].[CreateMerge]

    (@TableName NVARCHAR(256)

    ,@Schemaname NVARCHAR(256) = N'dbo'

    ,@ExcludeData INT = 0

    ,@ReverseOrder INT = 0

    ,@TopCount INT = 10

    ,@CustomWhere NVARCHAR(4000) = ''

    )

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE

    @i INT

    ,@sqlTextFirst VARCHAR(MAX)

    ,@sqlTextSecond VARCHAR(MAX)

    ,@justDataList VARCHAR(MAX)

    ,@EXECText NVARCHAR(MAX)

    ,@MergeText VARCHAR(MAX)

    ,@Filename VARCHAR(256)

    ,@Debug INT = 0;

    IF LEFT(@CustomWhere,6) <> 'WHERE ' AND LEN(@CustomWhere) > 1

    BEGIN

    SELECT 'Invalid WHERE Clause'

    RETURN

    END;

    IF OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL

    DROP TABLE #DataToMerge

    CREATE TABLE #DataToMerge

    (nDex INT IDENTITY

    ,UsingList NVARCHAR(MAX));

    WITH TableInfoStart

    AS (SELECT DISTINCT

    t.object_id

    ,s.name AS schemaName

    ,t.name AS tableName

    ,icc.name AS PK

    ,icc.is_identity AS PKisID

    ,STUFF((SELECT

    ',[' + c.Name + ']' AS [text()]

    FROM

    sys.COLUMNS AS c

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    FOR

    XML PATH('')

    ),1,1,'') AS columnlist

    ,'''' + CHAR(10) + ' ,('' + ' + STUFF((SELECT

    CASE WHEN st.precision = 0 OR

    st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes

    ' + '','' + ISNULL('''''''' + REPLACE([' + c.Name + '],'''''''','''''''''''') + '''''''',''NULL'')'

    ELSE

    ' + '','' + ISNULL(REPLACE(CAST([' + c.Name + '] AS VARCHAR(MAX)),'''''''',''''''''''''),''NULL'')'

    END AS [text()]

    FROM

    sys.COLUMNS AS c

    INNER JOIN sys.types AS st

    ON c.user_type_id = st.user_type_id

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    AND ( (c.name <> icc.name AND icc.is_identity = 1) OR icc.is_identity = 0)

    FOR

    XML PATH('')

    ),1,8,'') + ' + '')''' AS selectlist

    ,CHAR(10) + ' (' + STUFF((SELECT

    CASE WHEN st.precision = 0 OR

    st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes

    ','''+ c.Name +''''

    ELSE

    ','+ c.Name

    END AS [text()]

    FROM

    sys.COLUMNS AS c

    INNER JOIN sys.types AS st

    ON c.user_type_id = st.user_type_id

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    FOR

    XML PATH(''))

    ,1,1,'') + ') ---- Place your data here' AS justDatalist

    ,STUFF(STUFF((SELECT

    ' AND [TGT].[' + col.COLUMN_NAME + '] = ' + '[SRC].[' + col.COLUMN_NAME + ']' + CHAR(10) AS [text()]

    FROM

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab

    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

    ON Col.Constraint_Name = Tab.Constraint_Name

    AND Col.Table_Name = Tab.Table_Name

    AND Col.CONSTRAINT_SCHEMA = Tab.TABLE_SCHEMA

    WHERE

    tab.CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND tab.TABLE_SCHEMA = s.NAME

    AND tab.TABLE_NAME = t.name

    FOR

    XML PATH('')

    ),1,8,''),1,1,'') AS JoinClause

    FROM

    sys.tables AS t

    INNER JOIN sys.schemas AS s

    ON t.schema_id = s.schema_id

    INNER JOIN sys.indexes AS i

    ON i.object_id = t.object_id

    AND i.is_primary_key = 1

    INNER JOIN sys.index_columns AS ic

    ON i.object_id = ic.object_id

    AND i.index_id = ic.index_id

    INNER JOIN sys.columns AS icc

    ON ic.column_id = icc.column_id

    AND ic.object_id = icc.object_id

    WHERE

    s.name = @SchemaName

    AND

    t.name = @TableName)

    ,TableInfo

    AS (SELECT

    object_id

    ,schemaName

    ,tableName

    ,PK

    ,PKisID

    ,CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END AS columnList

    ,CASE WHEN PKisID=1 THEN REPLACE(selectlist,'[' + PK + '],','') ELSE selectlist END AS selectlist

    ,CASE WHEN PKisID=1 THEN REPLACE(justDatalist,',(' + PK + ',','(') ELSE justDatalist END AS justDatalist

    ,JoinClause

    FROM TableInfoStart)

    SELECT

    @FileName = 'C:\SupportingData\' + schemaName + '.' + tableName + '.sql'

    ,@sqlTextFirst =

    '/*' + CHAR(10) +

    schemaName + '.' + tableName + CHAR(10) +

    '*/' + CHAR(10) + CHAR(10) +

    ' MERGE INTO [' + schemaName + '].[' + tableName + '] AS [TGT]' + CHAR(10) +

    ' USING (VALUES '

    ,@sqlTextSecond = CHAR(10) +

    ' ) AS [SRC] ' + CHAR(10) +

    ' (' + columnList + ')' + CHAR(10) +

    ' ON' + JoinClause + CHAR(10) +

    ' ---- When the record does not exist... INSERT' + CHAR(10) +

    ' WHEN NOT MATCHED BY TARGET THEN' + CHAR(10) +

    ' INSERT ' + CHAR(10) +

    ---- handle the PK being an identitiy

    ' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ') ' + CHAR(10) +

    ' VALUES ' + CHAR(10) +

    ---- handle the PK being an identitiy

    ' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ');' + CHAR(10) +

    'GO' + CHAR(10)

    ,@justDataList = justDataList

    ,@EXECText = CHAR(10) + +

    CASE WHEN @TopCount = 1 THEN 'SELECT TOP (' + CAST(@TopCount AS VARCHAR(9)) + ') ' ELSE 'SELECT ' END

    + ISNULL(selectList,'') + CHAR(10) +

    'FROM [' + schemaName + '].[' + tableName + '] ' + ISNULL(@CustomWhere,'') + ' ORDER BY ' + ISNULL(PK,'1') +

    CASE WHEN @ReverseOrder = 1 THEN ' DESC' ELSE ' ASC' END

    FROM TableInfo AS s;

    PRINT @exectext

    IF @ExcludeData = 0

    BEGIN

    INSERT #DataToMerge

    EXEC sp_executesql @EXECText

    IF EXISTS (SELECT NULL FROM #DataToMerge AS dtm)

    BEGIN

    SELECT REPLACE(REPLACE(REPLACE(REPLACE(@sqlTextFirst +

    STUFF(STUFF((SELECT

    UsingList AS [text()]

    FROM

    #DataToMerge

    FOR XML PATH(''))

    ,1,1,''), 12,1,CHAR(10) + ' ')+

    @sqlTextSecond

    ,'USING (VALUES (','USING (VALUES ('), '<', '<'), '>', '>'), '&', '&')

    END

    ELSE

    SELECT 'No Data'

    END

    ELSE

    BEGIN

    SELECT

    REPLACE(@sqlTextFirst

    + @JustDataList

    + @sqlTextSecond ,'USING (VALUES (','USING (VALUES (')

    END;

    END

    GO

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Wow, this looks really risky.

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Iwas Bornready (9/8/2015)


    Wow, this looks really risky.

    Risky? It doesn't run any code. It simply creates the code for you. We've used in production for over a year now and it works beautifully.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • gserdijn

    Hall of Fame

    Points: 3204

    I tried the CreateMerge procedure since it might be useful.
    The procedure however has a few issues:
    [1] - the column order of the source is (sometimes?) alphabetically, and the identity column is not removed from the column list;
    [2] - an identity column is omitted from the source, but when it's also the primary key the join clause is incorrect;

    Table + dummy data (version: SQL Server 2012 SP3 CU9)
    CREATE TABLE dbo.languages (
      language_id INT IDENTITY NOT NULL CONSTRAINT pk_languages PRIMARY KEY CLUSTERED,
      isocode CHAR(2) NOT NULL,
      [language] VARCHAR(100) NOT NULL,
      in_use BIT NOT NULL CONSTRAINT df_languages_in_use DEFAULT 1);

    INSERT INTO dbo.languages (isocode, [language], in_use)
    VALUES
      ('en', 'English', 1),
      ('nl', 'Dutch', 1),
      ('pd', 'ProtoDutch (Dunglish)', 0);

    EXEC [Admin].CreateMerge @TableName = 'languages'

    The output:
    MERGE INTO [dbo].[languages] AS [TGT]
      USING (VALUES    
        ('en','English',1)
        ,('nl','Dutch',1)
        ,('pd','ProtoDutch (Dunglish)',0)
            ) AS [SRC]
                 ([in_use],[isocode],[language],[language_id]) /* wrong order, and identity column not removed */
        ON [TGT].[language_id] = [SRC].[language_id] /* problem */

      ---- When the record does not exist... Etcetera

    Issue [1] can easily be fixed by adding:
    ORDER BY c.column_id
    in:
    SELECT
      ',[' + c.Name + ']' AS [text()]
    FROM sys.columns AS c
    WHERE t.OBJECT_ID = c.OBJECT_ID
      AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS') 
      AND c.NAME NOT LIKE '%GUID'
    ORDER BY c.column_id /* + */
    FOR XML PATH('')

    The new output:
    MERGE INTO [dbo].[languages] AS [TGT]
      USING (VALUES    
        ('en','English',1)
        ,('nl','Dutch',1)
        ,('pd','ProtoDutch (Dunglish)',0)
            ) AS [SRC]
                 ([isocode],[language],[in_use]) /* better */
        ON [TGT].[language_id] = [SRC].[language_id] /* .. but still a problem */

      ---- When the record does not exist... Etcetera

    Issue [2]:  For tables without an identity property the proc yielded in a few simple tests a correct MERGE statement. In other cases the join clause will have to be modified manually. Well, as the author said: "This script does NOT handle all situations." It's given me some food for thought.  🙂



    Dutch Anti-RBAR League

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Thanks gserdijn for the catch and fix!

    It's also good to hear it's inspired your thinking of other uses.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply