Issue with composite key and order in values

[hidegh]

Had this table:

CREATE TABLE [zip].[ZipTzDstExtension](
	[TZ] [nvarchar](6) NOT NULL,
	[DST] [nvarchar](1) NOT NULL,
	[SQL] [nvarchar](100) NULL
 CONSTRAINT [PK_work] PRIMARY KEY CLUSTERED 
(
	[TZ] ASC,
	[DST] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

It generated the data below - all was OK, except the ORDER and this time not, but previously I had a break in the syntax as the 1st row was somehow moved into the middle (there was a comma missing and there was an extra one at the start)...

<?x ---

USE [Test]
GO

--MERGE generated by 'sp_generate_merge' stored procedure
--Originally by Vyas (http://vyaskn.tripod.com/code): sp_generate_inserts (build 22)
--Adapted for SQL Server 2008+ by Daniel Nolan (https://twitter.com/dnlnln)

SET NOCOUNT ON

DECLARE @mergeOutput TABLE ( [DMLAction] VARCHAR(6) );
MERGE INTO [zip].[ZipTzDstExtension] AS [Target]
USING (VALUES
  (N'EST+1',N'N',N'Atlantic Standard Time')
 ,(N'MST',N'N',N'US Mountain Standard Time')
 ,(N'PST-2',N'N',N'Hawaiian Standard Time')
 ,(N'PST-3',N'N',N'Samoa Standard Time')
 ,(N'PST-4',N'N',N'UTC+12')
 ,(N'PST-5',N'N',N'Central Pacific Standard Time')
 ,(N'PST-6',N'N',N'Vladivostok Standard Time')
 ,(N'PST-7',N'N',NULL)
 ,(N'CST',N'Y',N'Central Standard Time')
 ,(N'EST',N'Y',N'Eastern Standard Time')
 ,(N'EST+1',N'Y',NULL)
 ,(N'GMT+1',N'Y',NULL)
 ,(N'MST',N'Y',N'Mountain Standard Time')
 ,(N'PST',N'Y',N'Pacific Standard Time')
 ,(N'PST-1',N'Y',N'Alaskan Standard Time')
 ,(N'PST-2',N'Y',N'Aleutian Standard Time')
) AS [Source] ([TZ],[DST],[SQL])
ON ([Target].[DST] = [Source].[DST] AND [Target].[TZ] = [Source].[TZ])
WHEN MATCHED AND (
	NULLIF([Source].[SQL], [Target].[SQL]) IS NOT NULL OR NULLIF([Target].[SQL], [Source].[SQL]) IS NOT NULL) THEN
 UPDATE SET
  [Target].[SQL] = [Source].[SQL]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([TZ],[DST],[SQL])
 VALUES([Source].[TZ],[Source].[DST],[Source].[SQL])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE
OUTPUT $action INTO @mergeOutput;

DECLARE @mergeError int
 , @mergeCount int, @mergeCountIns int, @mergeCountUpd int, @mergeCountDel int
SELECT @mergeError = @@ERROR
SELECT @mergeCount = COUNT(1), @mergeCountIns = SUM(IIF([DMLAction] = 'INSERT', 1, 0)), @mergeCountUpd = SUM(IIF([DMLAction] = 'UPDATE', 1, 0)), @mergeCountDel = SUM (IIF([DMLAction] = 'DELETE', 1, 0)) FROM @mergeOutput
IF @mergeError != 0
 BEGIN
 PRINT 'ERROR OCCURRED IN MERGE FOR [zip].[ZipTzDstExtension]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
 END
ELSE
 BEGIN
 PRINT '[zip].[ZipTzDstExtension] rows affected by MERGE: ' + CAST(COALESCE(@mergeCount,0) AS VARCHAR(100)) + ' (Inserted: ' + CAST(COALESCE(@mergeCountIns,0) AS VARCHAR(100)) + '; Updated: ' + CAST(COALESCE(@mergeCountUpd,0) AS VARCHAR(100)) + '; Deleted: ' + CAST(COALESCE(@mergeCountDel,0) AS VARCHAR(100)) + ')' ;
 END
GO


SET NOCOUNT OFF
GO


?>

[dnlnln]

Looks like the primary key query is getting the list of columns back in reverse order. I'll look at adding an ORDER BY ORDINAL_POSITION on the information schema to see if that fixes it.

[dnlnln]

Resolved by #87 , thank you @b-twis for the contribution and @hidegh for the report