Is there a "Script table as - MERGE" function somewhere?

Question

In SSMS 2008 R2, when I right click on a table I see "Script Table as" then options for Insert and Update. But what about Merge? Merge is really just the two of these together.

Is there any tool I can get that will add that option? (So I can script a merge statement ready for me to add in source information (kind of like the Insert and Update scripts are ready for adding the data to insert or update).

Answer 1

There is no built-in functionality in SSMS that performs such operation(I guess it may be possible with external plugin).

There is a procedure sp_GenMerge(licensed under MIT license) written by Michał Gołoś that allows to script table with data as merge statement.

Sample scenario:

CREATE TABLE [Customer]  (
   ID                   INT                  IDENTITY(1,1) CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID),
   FIRSTNAME            NVARCHAR(30)         NOT NULL,
   LASTNAME             NVARCHAR(30)         NOT NULL,
   CITY                 NVARCHAR(30)         NULL,
   COUNTRY              NVARCHAR(30)         NULL,
   PHONE                NVARCHAR(20)         NULL
);

INSERT INTO [Customer] ([FirstName],[LastName],[City],[Country],[Phone])
VALUES('John','Smith','Berlin','Germany','12345'),('Cathrine','Fa','Brasilia','Brasil','(3) 4324-4723');

Basic SP call(it supports more options - see doc):

EXEC sp_GenMerge @source_table = 'dbo.Customer';

Output:

DECLARE @xml XML = N'
<!-- Insert the generated data here -->
';


MERGE INTO dbo.Customer AS Target
USING (SELECT x.value('(@ID)', 'int') AS [ID]
            , x.value('(@FIRSTNAME)', 'nvarchar(30)') AS [FIRSTNAME]
            , x.value('(@LASTNAME)', 'nvarchar(30)') AS [LASTNAME]
            , x.value('(@CITY)', 'nvarchar(30)') AS [CITY]
            , x.value('(@COUNTRY)', 'nvarchar(30)') AS [COUNTRY]
            , x.value('(@PHONE)', 'nvarchar(20)') AS [PHONE]
        FROM @xml.nodes('v') AS t(x)) AS Source ([ID], [FIRSTNAME], [LASTNAME], [CITY], [COUNTRY], [PHONE])
  ON (Target.[ID] = Source.[ID])
WHEN NOT MATCHED BY TARGET
THEN INSERT([FIRSTNAME]
          , [LASTNAME]
          , [CITY]
          , [COUNTRY]
          , [PHONE])
     VALUES(Source.[FIRSTNAME]
          , Source.[LASTNAME]
          , Source.[CITY]
          , Source.[COUNTRY]
          , Source.[PHONE])
WHEN MATCHED AND EXISTS (SELECT Target.[FIRSTNAME]
                              , Target.[LASTNAME]
                              , Target.[CITY]
                              , Target.[COUNTRY]
                              , Target.[PHONE]
                         EXCEPT
                         SELECT Source.[FIRSTNAME]
                              , Source.[LASTNAME]
                              , Source.[CITY]
                              , Source.[COUNTRY]
                              , Source.[PHONE])
THEN UPDATE SET Target.[FIRSTNAME] = Source.[FIRSTNAME]
              , Target.[LASTNAME] = Source.[LASTNAME]
              , Target.[CITY] = Source.[CITY]
              , Target.[COUNTRY] = Source.[COUNTRY]
              , Target.[PHONE] = Source.[PHONE];
GO

And scripted rows as XML payload:

<v ID="1" FIRSTNAME="John" LASTNAME="Smith" CITY="Berlin" COUNTRY="Germany" PHONE="12345" />
<v ID="2" FIRSTNAME="Cathrine" LASTNAME="Fa" CITY="Brasilia" COUNTRY="Brasil" PHONE="(3) 4324-4723" />

Answer 2

There is a very useful script on GitHub that I've been using with great success.

Generate SQL MERGE statements with Table data

Use Cases:

• Generate statements for static data tables, store the .SQL file in source control/add it to a Visual Studio Database Project and use it as part of your Dev/Test/Prod deployments. The generated statements are re-runnable, so you can make changes to the file and easily 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.

How Does It Work? The generated MERGE statement populates the target table to match the source data. This includes the removal of any excess rows that are not present in the source.

When the generated MERGE statement is executed, the following logic is applied based on whether a match is found:

• If the source row does not exist in the target table, an INSERT is performed
• If a given row in the target table does not exist in the source, a DELETE is performed
• If the source row already exists in the target table and has changed, an UPDATE is performed
• If the source row already exists in the target table but the data has not changed, no action is performed (configurable)

Usage:

1. Ensure that your SQL client is configured to send results to grid.
2. Execute the proc, providing the source table name as a parameter
3. Click the hyperlink within the result set.
4. Copy the SQL (excluding the Output tags) and paste into a new query window to execute.

Example: To generate a MERGE statement containing all data within the Person.AddressType table, excluding the ModifiedDate and rowguid columns:

EXEC AdventureWorks.dbo.sp_generate_merge @schema = 'Person', @table_name ='AddressType', @cols_to_exclude = '''ModifiedDate'',''rowguid'''

OUTPUT:

SET NOCOUNT ON
GO 
SET IDENTITY_INSERT [Person].[AddressType] ON
GO
MERGE INTO [Person].[AddressType] AS Target
USING (VALUES
  (1,'Billing')
 ,(2,'Home')
 ,(3,'Main Office')
 ,(4,'Primary')
 ,(5,'Shipping')
 ,(6,'Contact')
) AS Source ([AddressTypeID],[Name])
ON (Target.[AddressTypeID] = Source.[AddressTypeID])
WHEN MATCHED AND (
    NULLIF(Source.[Name], Target.[Name]) IS NOT NULL OR NULLIF(Target.[Name], Source.[Name]) IS NOT NULL) THEN
 UPDATE SET
 [Name] = Source.[Name]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([AddressTypeID],[Name])
 VALUES(Source.[AddressTypeID],Source.[Name])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE;

SET IDENTITY_INSERT [Person].[AddressType] OFF
GO
SET NOCOUNT OFF
GO

Answer 3

Until i know you can´t but there are some tips:

How to edit SSMS Script Table As templates?