2017年11月9日

Transact-SQLのカラム追加は少しだけ難しい(SQL Server)

 

(画像引用元：https://www.logicum.co/)

 

 

Transact-SQL(T-SQL)とは、マイクロソフトとSybaseが独自に拡張したSQL言語である。

マイクロソフトによる実装は Microsoft SQL Server として出荷されている。

(出典：Wikipedia)

 

 

SQL Serverで使用するT-SQLはOracleとPostgreSQLとかPostgreSQLとMySQLの違いのようなもので、

ほとんど呼称を変えただけのSQL言語の方言のようなものです。

なので、SELECTとかUPDATEとかの基本的なことは同じで、

たまにTOPが使えないとか、LIMITが使えないとかそういった状況になります。

因みに以前にもOracleでは通用しない方言(LIMIT句)についても書きました。

PostgreSQLやMySQLで使えるLimit句をOracle SQLで使う方法

 

 

今回はTableに新しくカラムを追加しようと思った時に発覚したT-SQLの仕様について書きます。

通常、Tableに対し新しくカラムを追加する場合は、

「ALTER TABLE (table) ADD (column)」を使用しますね。

 

 

実際の文法はこうです。

ALTER TABLE testTable ADD column2new

これで例えば列column0とcolumn1しかなかったテーブルが下記のようになります。

 

表：testTable

column0	column1	column2new
user1	tokyo	men
user2	saitama	women

 

 

しかしこれは列の最後尾に列を新しく追加する方法です。

それでは、列colmun0の後に追加するにはどうすれば良いのかというと下記のようになります。

 

ALTER TABLE testTable ADD column2new AFTER column0

このようにAFTER句を記述すると下記のようにcolumn0の後にcolumn2newを追加することができます。

 

表：testTable

column0	column2new	column1
user1	men	tokyo
user2	women	saitama

 

しかしなんとこのAFTER句はT-SQLでは使用ができません。

これではうっかり仕様が変わった日には大変なことになります。

 

それではどうするのかというと、

「レコードをテンポラリ(一時)テーブルに格納し、新しく列を追加したテーブルを作成し、テンポラリテーブルからレコードを戻す。」

そうです。中々面倒くさそうですね。

早速先述の表を参考にやってみましょう。

※テンポラリテーブルはテーブル名の頭に#を使用し作成します。

 

 // テンポラリテーブルを作成
SELECT * INTO #test
 // 元のテーブルを削除
DROP TABLE testTable
 // 新しいテーブルを作成
CREATE TABLE testTable
(
　column0 int NOT NULL,
　column2new nvarchar2(5),
　column1 nvarchar2(20)
)
 // 新しいテーブルにテンポラリテーブルからレコードを戻す
INSERT INTO testTable
SELECT
　t.column0,
　0,
　t.column1
FROM #test t
 // テンポラリテーブルを削除
DROP TABLE #test

 

テンポラリテーブルには”#test”のローカル(同一セッションのみ)と”##test”のグローバル(複数セッションを跨ぐ)があるそうです。

テンポラリテーブルを持ち続けるのは確認がしづらいことから名前空間を汚したりやっかいみたいなので、使い終わったら削除するとか、ローカルしか使わないとか必要に応じて使い分けると良いと思います。

 

MSはIEの件といい独自の仕様が多いのだなと思いました。

本日の記事は以上です。