使用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("--");

            }
             */
             
        }
    }
}