2015年4月4日土曜日

[MSAccess] SELECT INTO でデータ型を指定したい

Microsoft Access の SQL では、SELECT INTO ステートメントとは、テーブルを作成して検索結果を挿入します。作成するテーブルにおけるフィールドのデータ型は、クエリの元になるテーブルのフィールドのデータ型とフィールド サイズを継承しますが、SQL 関数などを使用すると適切なデータ型に判定されません。（多くはテキスト型になる）

■ データを直接指定した場合

以下の結果となりました。（Microsoft Access 2007）
SELECT
  0 As Col1,
  0.1 As Col2,
  50000 As Col3,
  '0' As Col4,
  #2015/04/01# As Col5,
  True As Col6,
  Null As Col7,
  IIF(True, 1) As Col8
INTO テーブル1
上記の実行結果
挿入する値データ型の判定結果
0長整数型 (Long)
0.110 進型 (Decimal)
50000長整数型 (Long)
'0'テキスト型 (Text)
#2015/04/01#日付/時刻型 (Date/Time)
True整数型 (Integer)
Nullバイナリ型 (Binary)
IIF関数長整数型 (Long)
今回は、それなりに判定されています。

■ データ型変換関数を使う場合

期待通りのデータ型に判定されていないなら、数値型等のデータ型を指定する場合は、データ型変換関数を使うことで判定を誘導できる。ただしデータ型変換関数では Null はエラーになってしまうため、Not Null 制約がある項目でしか使えない。
データ型変換関数データ型の判定結果
CBool(1)整数型 (Integer)
CByte(1)整数型 (Integer)
CCur(1)通貨型 (Currency)
CDate(1)日付/時刻型 (Date/Time)
CDbl(1)倍精度浮動小数点型 (Double)
CInt(1)整数型 (Integer)
CLng(1)長整数型 (Long)
CSng(1)倍精度浮動小数点型 (Double)
CVar(1)テキスト型 (Text)
CStr(1)テキスト型 (Text)
これも、それなりに判定されています。（完全ではない）

■ データ型変換関数を使う ＋ Null 対策あり

Null 対策が必要であれば、ひと工夫してみると・・・、Null の場合はこの足し算結果が Null になるが、実行時エラーにはならない。
1 + CBool(True)倍精度浮動小数点型 (Double)
1 + CByte(1)倍精度浮動小数点型 (Double)
1 + CDate(1)日付/時刻型 (Date/Time)
1 + CDbl(1)倍精度浮動小数点型 (Double)
1 + CInt(1)倍精度浮動小数点型 (Double)
1 + CLng(1)倍精度浮動小数点型 (Double)
1 + CSng(1)倍精度浮動小数点型 (Double)
ほとんど「倍精度浮動小数点型」に判定されてしまった。

■参考資料

SELECT.INTO ステートメント (Microsoft Access SQL)
https://msdn.microsoft.com/ja-jp/library/office/ff192059.aspx

Access クエリで、CDec() 関数を使用すると、エラー メッセージ:「指定した式に含まれる関数で、引数の数が一致しません。」
http://support.microsoft.com/ja-jp/kb/225931/ja

データ型変換関数 (Visual Basic)
https://msdn.microsoft.com/ja-jp/library/s2dy91zy.aspx

Microsoft Access SQL リファレンス
https://msdn.microsoft.com/ja-jp/library/office/dn123881.aspx
時刻: