2017 315,凤凰传奇是两口子,爱情保卫战20121124
作为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("--"); } */ } } }
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论