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).
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" />