Using T4 Template to Generate SQL Server Merge Satement

SQL Server merge statement is very powerful, but it is very time consuming to write. Using the following T4 template, you can get the code generated for you directly:

<#@ template debug="false" hostspecific="false" #>
<#@ output extension=".sql" #>
<#@ assembly name="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ assembly name="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ assembly name="System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ assembly name="System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Transactions" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Linq" #>
<#	var databaseName = "";
	var schemaName = "";
	var tableName = "";

	var connectionString = "";
#>
Table Name : <#= tableName #>
<#
	var columns = new Dictionary<String, int>();

	using (var conn = new SqlConnection(connectionString))
	{
		conn.Open();
		var cmd = new SqlCommand(
			String.Format(
			@"
				select DISTINCT c.column_name, 
				CASE WHEN OBJECTPROPERTY(OBJECT_ID(k.constraint_name), 'IsPrimaryKey') = 1 THEN 1 ELSE 0 END AS IsPrimary,
				c.DATA_TYPE,
				c.CHARACTER_MAXIMUM_LENGTH,
				c.IS_NULLABLE,
				c.ORDINAL_POSITION
				from information_schema.columns c
				LEFT join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
				on c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME
				where c.table_name = '{0}'
				order by c.ORDINAL_POSITION
			", tableName),
			conn);

		using (var reader = cmd.ExecuteReader())
        {
			while (reader.Read())
            {
				var name = reader.GetString(0).Trim();
				var type = reader.GetString(2).Trim().ToUpper();
				var length = reader.IsDBNull(3)?"": "(" + reader.GetInt32(3) + ")";
				var nullable = reader.GetString(4) == "YES" ? "," : " = NULL,";
				columns.Add(reader.GetString(0), reader.GetInt32(1));
#>
	@<#= name #> <#= type #><#= length #><#= nullable #>
<#
			}
        }
	}
#>

MERGE STATEMENT:	
	MERGE <#= schemaName #>.<#= tableName #> AS target
	USING
	(
		SELECT <#= String.Join(", ", (from column in columns select "@" + column.Key.Trim()).ToArray()) #>
	) AS source (<#= String.Join(", ", (from column in columns select column.Key.Trim()).ToArray()) #>)
	ON 
	(
<# 
	var keys = (from column in columns where column.Value == 1 select column.Key.Trim()).ToList();
	for (int index = 0; index < keys.Count(); index++) {
		var line = "source." + keys[index] + " = target." + keys[index];
		if (index < keys.Count() - 1)
		{
			line += " AND";
		}
#>
		<#= line #>
<#
	}
#>
	)
	WHEN MATCHED THEN
		UPDATE SET 
<# 
	var values = (from column in columns where column.Value != 1 select column.Key.Trim()).ToList();
	for (int index = 0; index < values.Count(); index++) {
		var line = "" + values[index] + " = source." + values[index];
		if (index < values.Count() - 1)
		{
			line += ",";
		}
#>
		<#= line #>
<#
	}
#>
	WHEN NOT MATCHED THEN
		INSERT (<#= String.Join(", ", (from column in columns select column.Key.Trim()).ToArray()) #>)
		VALUES (<#= String.Join(", ", (from column in columns select "source." + column.Key.Trim()).ToArray()) #>);