Using T4 Template to Generate SQL Server Merge Satement-优快云博客

本文介绍了一种使用T4模板来自动化生成SQL Server Merge Statement的方法，大幅节省了编码时间，适用于数据库迁移和数据同步场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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()) #>);