SQLServerで一時テーブルを使って更新を行う |
ユーザー定義テーブルをテーブル値パラメーターとして
利用する方法を紹介しました。
この方法はユーザー定義テーブルを
事前に作成しておく必要があります。
更新処理を行う直前に一時テーブルを作成して
そこに更新用データを入れる方法を紹介します。
テーブル定義とデータ
IDと文字列、整数を持つテーブルを作成します。
CREATE TABLE [dbo].[TEST_TABLE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[COL_STRING] [nvarchar](50) NULL,
[COL_INT] [int] NULL
) ON [PRIMARY]
さらにレコードを2件登録しておきます。
INSERT INTO TEST_TABLE(COL_STRING,COL_INT)
VALUES ('test',1);
INSERT INTO TEST_TABLE(COL_STRING,COL_INT)
VALUES ('dummy',2);
実装例
IDが1のレコードを更新し、
その結果をメッセージボックスに表示するプログラムです。
※エラー処理は行っていません。適宜入れてください。
//接続文字列をconfigファイルから取得
var connectionString =
ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
//ブログのセキュリティ回避のため、一部全角になってます
connection.Open();
using (var command = new SqlCommand())
{
//トランザクション作成
var tran = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = tran;
command.CommandType = CommandType.Text;
//一時表を作成
command.CommandText = @"
CREATE TABLE #TEMP
(
ID int NOT NULL,
COL1 nvarchar(50) NOT NULL,
COL2 int NOT NULL,
);
";
command.ExecuteNonQuery();
//更新情報を作成
var inTable = new DataTable();
inTable.Columns.Add("ID", typeof(int));
inTable.Columns.Add("COL1", typeof(string));
inTable.Columns.Add("COL2", typeof(int));
var row = inTable.NewRow();
row["ID"] = 1;
row["COL1"] = "update:" + DateTime.Now.ToString();
row["COL2"] = new Random().Next(100);
inTable.Rows.Add(row);
//一時表示に更新情報を登録
using (var bulkCopy = new SqlBulkCopy(connection,SqlBulkCopyOptions.Default,tran))
{
bulkCopy.DestinationTableName = "#TEMP";
bulkCopy.WriteToServer(inTable);
}
//一時表を使って、対象テーブル(TEST_TABLE)を更新する
command.CommandText = @"
UPDATE TEST_TABLE
SET COL_STRING=COL1, COL_INT=COL2
FROM TEST_TABLE
INNER JOIN #TEMP
ON TEST_TABLE.ID = #TEMP.ID;
";
var resultCount = command.ExecuteNonQuery();
//更新結果を取得するSQLを作成
var sql = @"SELECT * FROM TEST_TABLE;";
var adapter = new SqlDataAdapter(sql,connection);
adapter.SelectCommand.Transaction = tran;
//SQLの発行
var dataTable = new DataTable();
adapter.Fill(dataTable);
//発行結果取得
var message = new StringBuilder();
foreach (DataRow selRow in dataTable.Rows)
{
message.AppendLine(selRow["COL_STRING"].ToString() + "," + selRow["COL_INT"].ToString());
}
MessageBox.Show(message.ToString());
//トランザクションをコミット
tran.Commit();
}
}
更新後のレコードはこのようになります。
実装例のポイント
・トランザクション内で一時表を作成する
テーブル作成(CREATE TABLE)で
テーブル名の先頭に#をつけると一時表になります。
・一時テーブルにDataTableを登録する
SqlBulkCopyクラスを利用してバルクインサートします。
・一時テーブルを使って更新する
「FROM TEST_TABLE INNER JOIN #TEMP」のように
一時テーブルを結合して更新を行います。
さらなる工夫
登録対象の一時レコードやバルクインサートするDataTableを
クラスから作成などの工夫も出来そうですね。
参考になれば幸いです。
参考にさせていただいたサイト
Qiita
SQL ServerのUPDATE SET FROM JOIN (WHERE)の挙動を調べてみた
http://qiita.com/youcune/items/b1b66a709ceb7baae5fe
全ては時の中に…
【SQL Server】一時テーブルを作成する
http://blog.livedoor.jp/akf0/archives/51025718.html
よかったらクリックしてください。
にほんブログ村