使用SMO程序化生成SQL Server表数据
作为ETL的一部分,有时候就是需要把数据的Insert脚本生成出来,然后人肉拷贝到另一个地方执行。
熟悉SMSS的同学们都知道,有个生成脚本的任务,可以生成数据库的create脚本啊什么的,其实也能够生产表中的数据。
自动化的ETL总不能连导出数据都人肉。。。一是容易出错,二是太low了。
C#控制台代码可以搞定这些,直接上代码:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management; using Microsoft.SqlServer.Management.Sdk.Sfc; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { String todayDate = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year; String backupDirectory = "1a:\\DBBackup\\"; String backupFileName = backupDirectory + todayDate + ".sql"; if (File.Exists(backupFileName)) { File.Delete(backupFileName); } StreamWriter sw = File.CreateText(backupFileName); Console.WriteLine(backupFileName); Console.ReadKey(); Console.WriteLine("hello!"); //Console.ReadLine(); String dbName = "2oy"; // database name Server srv = new Server("3lba1"); // Reference the database. Database db = srv.Databases[dbName]; // Define a Scripter object and set the required scripting options. Scripter scrp = new Scripter(srv); scrp.Options.ScriptSchema = false; scrp.Options.ScriptDrops = false; scrp.Options.WithDependencies = false; scrp.Options.Indexes = false; // To include indexes scrp.Options.DriAllConstraints = false; // to include referential constraints in the script scrp.Options.ScriptData = true; //Data include!!!!!! //Iterate through the tables in database and script each one. foreach (Table tb in db.Tables) { if (!tb.IsSystemObject) { foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn })) { sw.WriteLine(s); sw.Flush(); } } } /* * 此方法不能生成带数据的脚本,但是可以生成schema脚本 foreach (Table tb in db.Tables) { System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[]{tb.Urn}); foreach (string st in sc) { Console.WriteLine(st); } Console.WriteLine("--"); } */ } } }