BCPを利用したSQL Azureへのデータインポート

#目的
SQL Azureのデータコピーor大量データの投入を行いたい。
WebJobとか、WorkerRoleで自動実行したい。
(・・・・泥臭い記事ばかり書いてます。)

#課題
SELECT...INTO使えないパターン(v12以前とか、サーバ間とか、テストデータ突っ込むとか)

#実現方法
BCP使う
[SQL Azureへのデータ転送を高速に行う](http://sqlazure.jp/b/sqlazure/sqlazure_teamblog/105/)
[SQL Server データベースの Azure SQL Database への移行](https://azure.microsoft.com/ja-jp/documentation/articles/sql-database-cloud-migrate/)
[データローディングパフォーマンスガイド](https://technet.microsoft.com/library/dd425070.aspx)
[BCPユーティリティ](https://msdn.microsoft.com/library/ms162802.aspx)

#テスト
コマンドプロンプトで動作確認

```BCPを用いたフルコピー.bat
bcp [db].dbo.SourceTable out D:\out.txt -S [server].database.secure.windows.net,1433 -U [user] -P [pw] -c -b 10000 -a 4000
```
```BCPを用いた指定カラムコピー.bat
bcp "select aaa,bbb,GETDATE() from dbo.SourceTable" queryout D:\out.txt -S [server].database.secure.windows.net -d [db] -U [user] -P [pw] -c
```

```BCPを用いたインサート.bat
bcp dbo.cTable in D:\out.txt -S [server].database.secure.windows.net,1433 -d [db] -U [user] -P [pw] -c
```

これが動けば、WebJobに載せるだけで動くはず(多分)



以下は、コピーする際に、Tableを作るサンプル。

#実際のソース
```main.
private static void Main()
{
    using (var conTarget = new SqlConnection(" [sqlの接続文字列] "))
    {
		//コピー先Table
        var tablename = "TargetTable" + DateTime.Now.ToString("yyyyMMddhhmmss");

		//コピー先Tableを作る
        var cmdtext = new StringBuilder();
        cmdtext.Append(@"CREATE TABLE [" + tablename + @"](");
        cmdtext.Append(@" [aaa] [nvarchar](128) NOT NULL,");
        cmdtext.Append(@" [bbb] [int] NOT NULL,");
        cmdtext.Append(@" [cdate] [datetime] NOT NULL,");
        cmdtext.Append(@" CONSTRAINT [PK_dbo." + tablename + @"] PRIMARY KEY CLUSTERED ( [aaa] ASC )");
        cmdtext.Append(@" WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))");

        var cmd = conTarget.CreateCommand();
        cmd.CommandText = cmdtext.ToString();
        conTarget.Open();
        cmd.ExecuteNonQuery();

        //BCPの設定
        var sourceServer = @"[server].database.secure.windows.net,1433";
        var sourceDb = @"[db]";
        var sourceUser = @"[user]";
        var sourcePw = @"[pw]";
        var sourceTable = @"[SourceTable]";

        var targetServer = @"[server].database.secure.windows.net,1433";
        var targetDb = @"[db]";
        var targetUser = @"[user]";
        var targetPw = @"[pw]";
        var targetTable = "dbo."+ tablename;

        //WebJobの作業フォルダ
        string appRoot = Path.GetTempPath();
        var textPath = Path.Combine(appRoot, @"out.txt");

        var outdb=new Bcp(sourceTable,sourceDb,sourceServer,sourceUser,sourcePw,textPath);
        outdb.CallBcp(false);

        var indb=new Bcp(targetTable,targetDb,targetServer,targetUser,targetPw,textPath);
        indb.CallBcp(true);

        //データインサート後にIndex作成
        var cmdmakeIndex = new StringBuilder();
        cmdmakeIndex.Append(@"CREATE INDEX IX_KEY_1 ON " + tablename + @"([aaa])");
        cmdmakeIndex.Append(@"CREATE INDEX IX_KEY_2 ON " + tablename + @"([cdate])");

        var cmd3 = conTarget.CreateCommand();
        cmd3.CommandText = cmdmakeIndex.ToString();
        cmd3.ExecuteNonQuery();
    }
}
```

```bcp.
public Bcp(string table, string db, string server, string user, string pw, string textPath)
{
    Table = table;
    Db = db;
    Server = server;
    User = user;
    Pw = pw;
    TextPath = textPath;
}

public void CallBcp(bool cmd)
{
    var arg="";
    if (cmd)
    {
        arg = this.Table
            + @" in "+ TextPath
            + @" -S "+Server
            + @" -d "+Db
            + @" -U "+User
            + @" -P "+Pw
            + @" -c";
    }
    else
    {
        arg = @"""select aaa,bbb,GETDATE() from "+Tabl+@""""
            + @" queryout "+ TextPath
            + @" -S "+Server
            + @" -d "+Db
            + @" -U "+User
            + @" -P "+Pw
            + @" -c";
    }
    var info = new ProcessStartInfo
    {
        FileName = "bcp",
        Arguments = arg,
        UseShellExecute = false,
        ErrorDialog = false,
        CreateNoWindow = true
    };

    using (var proc = new Process())
    {
        proc.StartInfo = info;
        proc.Start();
    }
}
```