SQL Servserでcreate Table

SQL Serverでテーブルを作るときの注意点など、書いていきます。主に自分の備忘録用です。

クエリからのテーブル作成

SQL文からテーブルを作成します。

create table testTable(
    --ここにカラム
);

create文です。

上の例だとtestTableという名前のテーブルが作られます。

名前等を間違えて作っても、削除した後、修正してもう一度実行すればいいので気楽に。

今回はBITUSDのヒストリカルデータを入手し、SQL Serverに登録します。

ヒストリカルデータの入手

BTCのヒストリカルデータを入手します。方法はいくつかありますが、今回は手っ取り早くサイトからダウンロードします。

Investing.comにアクセスします。

ログインします。アカウントを持ってない人は作ってしまいましょう。

ログインしたら、上のメニュータブから、"Markets"→"Cryptocurrency"→"BTCUSD"と進みます。

その後、中段のチャート上部にメニュータブがあります。そこの"General"から"Histrical Data"をクリックします。

するとチャートだった場所がテーブルに変わります。

テーブル右上のカレンダーをクリックし、欲しいデータの期間を設定します。

期間を設定したら、すぐ右側にある"Download Data"をクリックしてダウンロードします。

CSV形式でダウンロードされるはずです。確認してみましょう。

データのヘッダ部分は"Date"、"Price"、"Open"、"High"、"Low"、"Vol."、"Change %"です。

そしてデータの形式は、"Date"は"31-Dec-19″となっています。

セルの書式設定を確認すると、ユーザー定義の書式となっており、"d-mmm-yy"という形式となっています。

価格関係と"Change %"のカラムのデータ形式は小数点があるのでfloat型です。そして数値の3桁目にカンマが付いています。これは金額データを表しています。

“Vol."は、後ろの"K"がついているので、数値としては扱えないので文字列とみなします。

また同CSVファイルをメモ帳で確認してみます。

カラムの区切りは"カンマ"で行われています(フラグ)。そしてレコードの切り替えは改行です。

テーブル作成

今度はSQL文にてカラムの設定を行います。

データの形式は上記CSVのデータ型に注意します。

create table testDB001.dbo.BTCUSDDailyPrice(
	"Date" date NOT NULL,
	Price float NOT NULL,
	"Open" float NOT NULL,
	"High" float NOT NULL,
	"Low" float NOT NULL,
	"Vol." varchar(50) NOT NULL,
	"Change%" varchar(50) NOT NULL,
);

カラムの名前を"(ダブルクオーテーション)で囲っているのは、その文字列が予約語だからです。

“Date"や"High"などは別に"で囲む必要はないのですが、"Open"はエラーになってしまいます。またエラーが起きなくても予約語をカラム名のする場合は、バグを回避するためにも、"で囲った方が良いかもしれません。

“NOT NULL"と設定していると、データを登録(insert)する際、そのカラムにNULLのデータがあると登録を受け付けません。

データの登録

それでは、ダウンロードしたファイルをバルクインサートで登録したいと思います。

バルクインサートの構文は次の通りです。

bulk insert testDB001.dbo.BTCUSDDailyPrice
from 'FILE PATH'
with (
    datafiletype = 'ファイルの型の指定',
    fieldterminator = 'カラム区切り文字の指定'
);

“FILE PATH"は読み込むCSVファイルの場所を指定します。

“datafiletype"はファイルの型を指定します。

  • cha: 文字形式
  • widechar: Unicode文字
  • native: ネイティブデータ型
  • widenative: ネイティブデータ型、Unicode文字

今回はテキストなので"cha"にします。日本語が混在していると、「codepage = '65001’」を"datafiletype"の後に追加書込みしないと読み込んでもらえないそうです。

“rowterminator"はフィールドの区切る文字を指定します。今回は","(カンマ)でした。

・・・

あ、数値の中にカンマが使われていることを忘れていました。

調べたら公式にもちゃんと注意書きされていました。

こういう事件は多々あるので注意しましょう。

そこで今回の解決策としては、CSVを編集しそれをバルクインサートすること、SSMS側からインポートしてしまうの二通りあります。

しんどいので今回はSSMSから直接インポートしてしまいます。

SSMSでインポートするデータベースのアイコンを右クリックし、タスク→フラットファイルのインポートを選びます。

インポート用のウィンドウが開きます。"はじめに"の項目は「次へ」で大丈夫です。

“入力ファイルの指定"では"ファイルをインポートする場所"と"新しいテーブル名"をしていします。"ファイルをインポートする場所"はCSVファイルのディレクトリです。"参照"からから指定しましょう。"新しいテーブル名"は適当につけましょう。あとで変更できます。

「次へ」をクリックしましょう。

“データのプレビュー"の項目です。カラム名やフィールドの区切りにおかしなところがないか確認しましょう。

問題なければ「次へ」をクリックします。

“列の変更"項目です。ここでは"Vol"のデータ型を変更します。"decimal(4,2)"から"decimal(8,2)"に変更します。また"NULLを許容"にチェックします。

理由はそのまま変更せずに実行すると、"Vol"のデータに5桁のものがあること、そして"Vol"のデータにNULLが含まれているため、エラーになるからです。

以上の変更を行い、「次へ」をクリックします。

“概要"の項目も特に問題ないので「次へ」をクリックします。

“結果"の項目です。今回は成功となりました。

エラーになるとここにどの部分でエラーになったのか詳細を教えてくれます。そしてエラーになったときは、データベース等に登録はされません。

では登録されたか確認してみます。

大丈夫のようです。

今度余力があれば、CSVをバルクインサートで登録する部分をやります。