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

SQL Server

SQL Server
データベース

公開日時: 2020-08-20 　更新日時: 2022-06-09

この記事でわかること

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

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

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

実行環境
・SQL Server 2017

一時テーブルの基本

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

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

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

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

一時テーブルの作成方法

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

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

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

CREATE TABLE #TEMPORARY_TABLE_1(
     ID      int NOT NULL
    ,COUNTRY varchar(30) NULL
    ,CITY    varchar(30) NULL
)

CREATE TABLE #TEMPORARY_TABLE_1(

ID int NOT NULL

,COUNTRY varchar(30) NULL

,CITY varchar(30) NULL

)

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

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

CREATE TABLE ##TEMPORARY_TABLE_2(
     ID      int NOT NULL
    ,COUNTRY varchar(30) NULL
    ,CITY    varchar(30) NULL
)

CREATE TABLE ##TEMPORARY_TABLE_2(

ID int NOT NULL

,COUNTRY varchar(30) NULL

,CITY varchar(30) NULL

)

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

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

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

ALTER TABLE #TEMPORARY_TABLE_1 ADD PRIMARY KEY CLUSTERED 
(
    ID ASC
)

ALTER TABLE #TEMPORARY_TABLE_1 ADD PRIMARY KEY CLUSTERED

(

ID ASC

)

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

CREATE NONCLUSTERED INDEX TEMPORARY_INDEX_1 ON #TEMPORARY_TABLE_1
(
 ID ASC
)

CREATE NONCLUSTERED INDEX TEMPORARY_INDEX_1 ON #TEMPORARY_TABLE_1

(

ID ASC

)

INSERT INTO #TEMPORARY_TABLE_1 Values(1,'Japan','TOKYO')
INSERT INTO #TEMPORARY_TABLE_1 Values(2,'USA','San Francisco')
SELECT * FROM #TEMPORARY_TABLE_1

INSERT INTO #TEMPORARY_TABLE_1 Values(1,'Japan','TOKYO')

INSERT INTO #TEMPORARY_TABLE_1 Values(2,'USA','San Francisco')

SELECT * FROM #TEMPORARY_TABLE_1

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

一時テーブルの削除方法

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

IF OBJECT_ID(N'tempdb..#TEMPORARY_TABLE_1', N'U') IS NOT NULL
DROP TABLE #TEMPORARY_TABLE_1;

1 2	IF OBJECT_ID(N'tempdb..#TEMPORARY_TABLE_1', N'U') IS NOT NULL DROP TABLE #TEMPORARY_TABLE_1;

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

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

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

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

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

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

FEnet.NETナビ・.NETコラムは株式会社オープンアップシステムが運営しています。

株式会社オープンアップシステムはこんな会社です

秋葉原オフィスには株式会社オープンアップシステムをはじめグループのIT企業が集結！
数多くのエンジニアが集まります。

スマホアプリから業務系システムまで

スマホアプリから業務系システムまで開発案件多数。システムエンジニア・プログラマーとしての多彩なキャリアパスがあります。
充実した研修制度

毎年、IT技術のトレンドや社員の要望に合わせて、カリキュラムを刷新し展開しています。社内講師の丁寧なサポートを受けながら、自分のペースで学ぶことができます。
資格取得を応援

スキルアップしたい社員を応援するために資格取得一時金制度を設けています。受験料(実費)と合わせて資格レベルに合わせた最大10万円の一時金も支給しています。
東証プライム上場企業グループ

オープンアップシステムは東証プライム上場「株式会社オープンアップグループ」のグループ企業です。

安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。

株式会社オープンアップシステムに興味を持った方へ

株式会社オープンアップシステムでは、開発系エンジニア・プログラマを募集しています。

年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
まずは話だけでも聞いてみたい場合もOK。お気軽にご登録ください。

株式会社オープンアップシステムへ応募する
会員登録不要! カンタン45秒!

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

一時テーブルの基本

一時テーブルの作成方法

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

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

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

一時テーブルの削除方法

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

株式会社オープンアップシステムはこんな会社です

スマホアプリから業務系システムまで

充実した研修制度

資格取得を応援

東証プライム上場企業グループ

株式会社オープンアップシステムに興味を持った方へ

新着案件New Job

開発エンジニア/東京都品川区/【WEB面談可】/在宅ワーク

遠隔テストサービス機能改修/JavaScript/東京都港区/【WEB面談可】/テレワーク

病院内システムの不具合対応、保守/東京都豊島区/【WEB面談可】/テレワーク

開発/JavaScript/東京都豊島区/【WEB面談可】/テレワーク

債権債務システム追加開発/東京都文京区/【WEB面談可】/在宅勤務

PMO/東京都豊島区/【WEB面談可】/在宅勤務

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

一時テーブルの基本

一時テーブルの作成方法

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

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

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

一時テーブルの削除方法

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

株式会社オープンアップシステムはこんな会社です

スマホアプリから業務系システムまで

充実した研修制度

資格取得を応援

東証プライム上場企業グループ

株式会社オープンアップシステムに興味を持った方へ

新着案件New Job

開発エンジニア/東京都品川区/【WEB面談可】/在宅ワーク

遠隔テストサービス機能改修/JavaScript/東京都港区/【WEB面談可】/テレワーク

病院内システムの不具合対応、保守/東京都豊島区/【WEB面談可】/テレワーク

開発/JavaScript/東京都豊島区/【WEB面談可】/テレワーク

債権債務システム追加開発/東京都文京区/【WEB面談可】/在宅勤務

PMO/東京都豊島区/【WEB面談可】/在宅勤務

関連記事Related Posts

SQL Server 2017でスキーマを作成する方法

SQL Server 2017でデータベースユーザーを作成する方法

SQL Serverのテーブルにカラムを追加する方法

SQLでのインデックス作成方法｜SQLServerなどのDBでのやり方を解説

SQL Serverで文字列を切り出す方法。SUBSTRINGの使い方