SQL Serverで一時テーブルを使う。作成と削除の方法を解説

SQL Serverを活用する場合、ストアドプロシージャを使用して、データベースサイドでデータの取得や更新などのプログラミングを行うことがあります。ストアドプロシージャではT-SQLを使用しますが、よく利用するオブジェクトに「一時テーブル」があります。この一時テーブルは、業務アプリを構築する際に役立ちますので、使い方をマスターしておきましょう。

SE

売上テーブルからデータを取得して、売上データを加工し、帳票に出力したいのですが、加工したデータをどこかに保存しておきたいです。どうやって実装すれば良いのでしょうか。

PL

その場合には、一時テーブルを使いましょう。ストアドプロシージャを活用するならば、覚えておきたいところですね。

実行環境
・SQL Server 2017

一時テーブルの基本

一時テーブルは tempdbに格納されます。この一時テーブルには2種類のテーブルが存在します。それが「ローカル一時テーブル」と「グローバル一時テーブル」です。
2種類の一時テーブルでは、名前、表示設定、可用性がそれぞれ異なります。

ローカル一時テーブルを作成する際には、テーブル名の先頭に「#」を付与します。作成したユーザーの現在の接続でのみ表示可能で、このユーザーがSQL Serverのインスタンスから切断すると削除されます。

対するグローバル一時テーブルは、テーブル名の先頭に「##」を付与します。作成されるとすべてのユーザーに表示されます。このグローバル一時テーブルを参照するすべてのユーザーが SQL Serverのインスタンスから切断すると削除されます。

一時テーブルをよく利用する場面は、ストアドプロシージャでプログラミングを行う時です。業務テーブルからデータを取得して、加工したデータを一時的にどこかに保管しておきたい場合などに活用するのが一時テーブルです。多くの場合、ローカル一時テーブルでコーディングを行えば事足りますが、グローバル一時テーブルが必要な場面も存在します。両方できるようにしておいて損はないでしょう。

一時テーブルの作成方法

それでは早速一時テーブルを作成してみましょう。ローカルとグローバル両方の作成方法を記載しますので、参考にしてください。

ローカル一時テーブルを作成する方法

CREATE TABLE命令でテーブルを作成できますが、その際にテーブル名の前に「#」を付加します。

グローバル一時テーブルを作成する方法

同じくCREATE TABLE命令でテーブルを作成します。今度はテーブル名の前に「##」を付加します。

CREATE TABLEをする際に、「#」or「##」がなければ一時テーブルでなく、物理的に存在するテーブルが出来上がってしまいます。本稼働するシステムで予期せぬテーブルが出来上がるのはインシデントです。くれぐれも「#」or「##」を付加することは忘れてはいけません。

一時テーブルに主キーやINDEX、データを追加する

上述で作成できた一時テーブルにデータを追加します。一時テーブルにも従来のテーブルのようにキーやインデックスも追加できますので、ここで合わせて確認しておきましょう。

PRIMARY KEYの設定がなくてもINDEXは設定できます。INDEX名はご自由にお付けください。

通常のテーブルと同じようにINSERT文を利用してデータを設定できます。

一時テーブルの削除方法

それでは最後に一時テーブルを削除します。SQL Serverのインスタンスから切断すると自動的に削除はされますが、削除方法も覚えておきましょう。
ここでは一時テーブルの存在をまず確認し、存在すれば削除するといったT-SQLをコーディングします。

一時テーブルの存在確認を行うIF文などの構文は、積極的に活用することをおすすめします。

SE

一時テーブルってとても便利ですね。物理的にテーブルを増やさなくても対応できるのは非常にありがたいです。これからもどんどん使って開発を進めていきます。

PL

複数のテーブルを外部結合、UNION 、副問い合わせなどを利用して複雑なSQLコーディングを行うよりも、一時テーブルを作成して何度かに分けて INSERT やUPDATE 、 SELECT を行う方がよりレスポンスが速いことだってあります。開発の状況に応じて活用していきましょう。

一時テーブルは上手に使いこなしましょう

実際のトランザクションテーブルやマスタテーブルですと、キー情報を持たせるならば、テーブル同士の紐づけなど考慮しなければならないことは多くあります。しかし、一時テーブルではそういった考慮は特に不要です。もちろんここで紹介したキーやINDEXを付加して使うことも可能です。状況に応じて柔軟に一時テーブルを活用していきましょう。

>>>SQL Serverの案件を探すならFEnet .NET Navi