作為ETL的一部分，有時(shí)候就是需要把數(shù)據(jù)的Insert腳本生成出來，然后人肉拷貝到另一個(gè)地方執(zhí)行。
熟悉SMSS的同學(xué)們都知道，有個(gè)生成腳本的任務(wù)，可以生成數(shù)據(jù)庫的create腳本啊什么的，其實(shí)也能夠生產(chǎn)表中的數(shù)據(jù)。
自動化的ETL總不能連導(dǎo)出數(shù)據(jù)都人肉。。。一是容易出錯，二是太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(); } } } /* * 此方法不能生成帶數(shù)據(jù)的腳本，但是可以生成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("--"); } */ } } }
