読者です 読者をやめる 読者になる 読者になる

Azure SQL データベースでは SqlBulkCopy で一時テーブルにデータを書き込めない

.net

集計中のデータを SqlBulkCopy を使って一時テーブルに流し込もうとしたら、

目的のテーブル '#tblae9a0940ec5d4a81912e3598ba6c24cf' にアクセスできません。

っていうエラーが発生した。

原因を調べていてたどり着いた MSDN フォーラムの投稿では、

これは Azure SQL データベースの既知の問題。Azure SQL データベースがサポートしていないストアドプロシージャを SqlBulkCopy が一時テーブルにアクセスするとき使おうとしているのが原因。

みたいな回答がしてある。上記はおもいっきり意訳だけど。原文は下記ページを参照。

Microsoft Connect でも報告されていた。

現時点で使えないのはどうしようもないので、ひとまず SQL で INSERT するしかないか。

検証で使ったサンプルコードを貼っておく。ちなみに、SELECT INTO ではなく DataReader を使っている理由は、DataReader のラッパーで集計データを加工しながら一時テーブルに流し込みたかったから。

using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace SqlAzureTempTableSample
{
    class Program
    {
        static void Main(string[] args)
        {
            var localTempableName = "#tbl" + Guid.NewGuid().ToString().Replace("-", "");
            var connectionString = GetConnectionString(CONNECTION_STRING_NAME);

            using (var connection = OpenConnection(connectionString))
            using (var tmpConnection = OpenConnection(connectionString))
            {
                // 一時テーブル作成
                using (var tmpCmd = tmpConnection.CreateCommand())
                {
                    tmpCmd.CommandText = string.Format(
                        @"create table {0} (
                              PRODUCTKIND smallint,
                              MENUID int
                          )",
                        localTempableName);
                    tmpCmd.ExecuteNonQuery();
                }

                // 集計
                DbDataReader reader;
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = string.Format(
                        @"select top 10
                                 PRODUCTKIND,
                                 MENUID
                          from MENU"
                    );
                    reader = cmd.ExecuteReader();
                }

                // 一時テーブルにデータを挿入
                using (reader)
                {
                    // SqlBulkCopy で一時テーブルにデータを挿入できない
                    //using (var bulkCopy = new SqlBulkCopy((SqlConnection)tmpConnection))
                    //{
                    //    bulkCopy.DestinationTableName = localTempableName;
                    //    bulkCopy.WriteToServer(reader);
                    //}

                    // SqlBulkCopy が使えないので、DataReader の内容を
                    // SQL で一時テーブルに挿入する
                    while (reader.Read())
                    {
                        using (var tmpCmd = tmpConnection.CreateCommand())
                        {
                            tmpCmd.CommandText = string.Format(
                                @"INSERT {0} (PRODUCTKIND, MENUID)
                                  VALUES (@p1, @p2)",
                                localTempableName);
                            tmpCmd.Parameters.Add(new SqlParameter()
                            {
                                ParameterName = "@p1",
                                Value = reader.GetInt16(0),
                                DbType = DbType.Int16,
                            });
                            tmpCmd.Parameters.Add(new SqlParameter()
                            {
                                ParameterName = "@p2",
                                Value = reader.GetInt32(1),
                                DbType = DbType.Int32,
                            });
                            tmpCmd.ExecuteNonQuery();
                        }
                    }
                }

                // 一時テーブルの内容を表示する
                using (var tmpCmd = tmpConnection.CreateCommand())
                {
                    tmpCmd.CommandText = string.Format(
                        "select * from {0}", localTempableName);
                    using (var tmpReader = tmpCmd.ExecuteReader())
                    {
                        while (tmpReader.Read())
                        {
                            Console.WriteLine("{0} | {1}", tmpReader.GetInt16(0), tmpReader.GetInt32(1));
                        }
                    }
                }
            }
            Console.ReadLine();
        }

        private const string CONNECTION_STRING_NAME = "SQLAzure";

        private static string GetConnectionString(string connectionStringName)
        {
            return ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

        private static DbConnection OpenConnection(string connectionString)
        {
            var connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }
    }
}