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. 🙂