• 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