dnlnln / generate-sql-merge

Generate SQL MERGE statements with Table data
MIT License
337 stars 151 forks source link

Add @quiet parameter to suppress informational messages and warnings #97

Closed dnlnln closed 1 year ago

dnlnln commented 1 year ago

If @quiet=1 is supplied, sp_generate_merge won't output warning messages when executed. Additionally, the generated MERGE will exclude the usual PRINT statements and will look something like this:

USE [AdventureWorks]
GO

SET NOCOUNT ON

SET IDENTITY_INSERT [Person].[AddressType] ON

MERGE INTO [Person].[AddressType] AS [Target]
USING (VALUES
  (1,N'Billing',N'B84F78B1-4EFE-4A0E-8CB7-70E9F112F886','2008-04-30T00:00:00')
 ,(2,N'Home',N'41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2','2008-04-30T00:00:00')
 ,(3,N'Main Office',N'8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575','2008-04-30T00:00:00')
 ,(4,N'Primary',N'24CB3088-4345-47C4-86C5-17B535133D1E','2008-04-30T00:00:00')
 ,(5,N'Shipping',N'B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5','2008-04-30T00:00:00')
 ,(6,N'Archive',N'A67F238A-5BA2-444B-966C-0467ED9C427F','2008-04-30T00:00:00')
) AS [Source] ([AddressTypeID],[Name],[rowguid],[ModifiedDate])
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 OR 
    NULLIF([Source].[rowguid], [Target].[rowguid]) IS NOT NULL OR NULLIF([Target].[rowguid], [Source].[rowguid]) IS NOT NULL OR 
    NULLIF([Source].[ModifiedDate], [Target].[ModifiedDate]) IS NOT NULL OR NULLIF([Target].[ModifiedDate], [Source].[ModifiedDate]) IS NOT NULL) THEN
 UPDATE SET
  [Target].[Name] = [Source].[Name], 
  [Target].[rowguid] = [Source].[rowguid], 
  [Target].[ModifiedDate] = [Source].[ModifiedDate]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([AddressTypeID],[Name],[rowguid],[ModifiedDate])
 VALUES([Source].[AddressTypeID],[Source].[Name],[Source].[rowguid],[Source].[ModifiedDate])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE;

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

Additional change: Fix for XML columns

This PR also contains a fix where invalid SQL was generated on certain XML columns, in particular when sp_generate_merge is executed against a database that was created via the DBCC CLONEDATABASE command.