如何使用SMO生成先DELETE表数据再INSERT的SQL Server脚本

初始配置与问题 #

初始使用如下ScriptingOptions配置生成数据脚本：

ScriptingOptions options = new ScriptingOptions();
options.ScriptSchema = false;
options.ScriptData = true;
options.ScriptForCreateDrop = true;
options.WithDependencies = true;

最终输出仅包含INSERT插入语句，示例：

INSERT [t].[Table] ([f], [f1], [f2], [f3], [f4], [f5], [f6]) VALUES (N'SomeData', N'SomeData', N'SomeData', N'SomeData', CAST(N'SomeData' AS DateTime2), CAST(N'SomeData' AS DateTime2), NULL)

预期效果为每张表的INSERT语句前，自动追加对应表的DELETE清空语句，格式如下：

DELETE FROM [t].[Table]

INSERT [t].[Table] ([f], [f1]) VALUES (N'SomeData', N'SomeData')

实现方法 #

SMO没有提供单一配置项直接实现上述效果，需要分两套独立配置分别生成删除、插入脚本，再按正确顺序拼接：

• 第一套配置专门生成DELETE清空语句，核心是开启ScriptDrops属性：

ScriptingOptions optionsForClearData = new ScriptingOptions();
optionsForClearData.ScriptSchema = false;
optionsForClearData.ScriptData = true;
optionsForClearData.ScriptForCreateDrop = true;
optionsForClearData.ScriptDrops = true;

注意：生成DELETE语句时必须按表依赖关系反向遍历表，避免外键约束导致删除执行失败。

• 第二套保留原有配置，用于生成INSERT数据脚本，生成时按依赖正序遍历表，保证插入顺序符合外键约束要求。
• 脚本拼接顺序：先放所有反向遍历生成的DELETE语句，再放所有正序遍历生成的INSERT语句，合并后即为符合预期的完整SQL脚本。

测试通过的实现代码 #

var tt = new StringCollection();
var s = new List<string>();
// 反向遍历表生成DELETE语句，规避外键约束报错
foreach (var tbl in Tables.Reverse())
{
    s.AddRange(dbs.Tables[tbl].EnumScript(optionsForClearData));
}
// 正序遍历表生成INSERT语句，保证依赖顺序正确
foreach (var tbl in Tables)
{
    s.AddRange(dbs.Tables[tbl].EnumScript(options));
}
tt.AddRange(s.ToArray());

MemoryStream ms = new MemoryStream();
TextWriter tw = new StreamWriter(ms);

var text = new string[tt.Count];
tt.CopyTo(text, 0);
tw.Write(string.Join("\n",text));
tw.Flush();
ms.Position = 0;
// 返回生成的sql文件
return new FormFile(ms, 0, ms.Length, "backup", FileName+".sql");

注：以上为原始测试代码，正式环境使用时需要为流操作添加using语句包裹，避免资源泄漏。

内容的提问来源于stack exchange，提问作者What was THAT