SQL ServerでSelectした結果でUpdateするSQL

SQL Serverで、Selectした結果でUpdateするためには以下のようなクエリを書きます。

update T1
set T1.C1 = T2.C2
from TABLE1 T1
inner join TABLE2 T2
on T1.KEY = T2.KEY

TABLE2をSelectした結果のC2フィールドの値で、TABLE1のC1フィールドを更新します。
この時、二つのテーブルをKEYフィールドで連結して、どのレコードを対応付けるかを指定しています。
もしも使えるようなら、このままコピペして、TABLE1、TABLE2、KEY、C1、C2あたりを書き換えて使ってもいいと思います。

inner joinではなくleft outer joinを使うとTABLE1のすべての行を更新しますが、TABLE2に該当するレコードがないときはNULLになります。

SQL Serverでデータベースの復元に失敗

SQL Serverでデータベースの復元をすると
---
サーバー'XXXX\YYYY'の復元に失敗しました。(Microsoft.SqlServer.SmoExtended)
追加情報:
System.Data.SqlClient.SqlError:ファイル'C:\Program Files\Microsoft SQL Server\...\DATA\ZZZ.MDF'が'AAAA'と'BBBB'から要求されました。WITH MOVE 句を私用して、1つ以上のファイルを再配置できます。
(Microsoft.SqlServer.Smo)
---
というメッセージのダイアログが表示されて、失敗しました。

スクリプトに変えて実行しても
---
ファイル 'xxx.MDF' が 'xxx_Log'(2) と 'xxx_Data'(1) から要求されました。WITH MOVE 句を使用して、1 つ以上のファイルを再配置できます。
メッセージ 3013、レベル 16、状態 1、行 1
RESTORE DATABASE が異常終了しています。
---
のようなメッセージが表示されて復元に失敗します。

原因は復元先のファイル名が行データとログで同じだからでした。
「データベースの復元」のダイアログにあるオプションの中の復元先のファイル名をどちらか変えることで復元できました。

Microsoft SQL ServerでコマンドプロンプトからSQLを実行する方法

SQL ServerがインストールされたパソコンでコマンドプロンプトからSQLを実行するにはsqlcmdを使う。
バッチファイルに組み込んで使うときにも便利だ。

> sqlcmd -S SERVER -d USE_DATABASE_NAME -U LOGIN_ID -P PASSWORD -Q "select * from table"

SERVER はサーバー名もしくはサーバー名とインスタンス名。例えば「hostname￥instance」のように書く。
USE_DATABASE_NAME はデーターベース名
LOGIN_ID はSQL ServerのログインID
PASSWORD はSQL Serverのパスワード

クエリの出力結果をファイルに保存したいときは、

> sqlcmd -S SERVER -d USE_DATABASE_NAME -U LOGIN_ID -P PASSWORD -Q "select * from table" > result.txt

のようにして、ファイルへリダイレクトするとよいだろう。

select句だけでなく、delete、insert、truncate等、登録、更新系のSQLも実行できる。
タスクに登録してスケジュール化したバッチファイルで使うようなこともできるだろう。

SQL ServerのSQLで西暦を和暦に変換する方法

SQL ServerのSQLで8桁のYYYYMMDD形式の西暦を和暦に変換する。

select case
when BIRTHDAY > '19890107' then '平成'
when BIRTHDAY > '19261224' then '昭和'
when BIRTHDAY > '19120729' then '大正'
when BIRTHDAY > '18680124' then '明治'
else '0'
end 年号,
case
when BIRTHDAY > '19890107' then BIRTHDAY - 19880000
when BIRTHDAY > '19261224' then BIRTHDAY - 19250000
when BIRTHDAY > '19120729' then BIRTHDAY - 19110000
when BIRTHDAY > '18680124' then BIRTHDAY - 18670000
end 年月日
from (select '19990123' BIRTHDAY) DUMMY

実行結果は以下の通り

年号 年月日
---- -----------
平成 110123

(1 行処理されました)

年号と和暦の年月日を出力する。
このSQLでは年号を「平成」とか「昭和」という文字列で出しているが、実際はコードに変換するようなことの方が多いかもしれない。

西暦4桁の年だけで和暦に変換するには基準となる年月を定める必要がある。

select case
when BIRTHDAY >= '1989' then '平成'
when BIRTHDAY >= '1926' then '昭和'
when BIRTHDAY >= '1912' then '大正'
when BIRTHDAY >= '1868' then '明治'
else '0'
end 年号,
case
when BIRTHDAY >= '1989' then BIRTHDAY - 1988
when BIRTHDAY >= '1926' then BIRTHDAY - 1925
when BIRTHDAY >= '1912' then BIRTHDAY - 1911
when BIRTHDAY >= '1868' then BIRTHDAY - 1867
end 年
from (select '1999' BIRTHDAY) DUMMY

実行結果

年号 年
---- -----------
平成 11

(1 行処理されました)

この場合12月24日～12月31日のいずれかの日を基準とする場合には正常な結果になるが、それ以外で使うには、上記SQLの数字部分を書き換える必要がある。

SQL Server2000でDTSのインポートのテーブル指定が面倒くさい

SQL Server2000 の SQL Server Enterprise Manager。DTSによるインポートという機能があり、別のデータベースからテーブルやビューをコピーすることができる。データベースのまるごとコピーなら、バックアップと復元を使う方法もあるが、ネットワーク経由だとバックアップファイルのコピーが必要になるためセキュリティ上許されないことがある。そこでポート1433を開放して、ネットワークごしにコピーするためのひとつの選択肢となる。

この機能、SQL Server2000で行おうとするとかなり使いにくい。
「データ変換サービス インポート/エクスポート ウィザード」を使い、「変換元データベースからテーブルとビューをコピー」というので行うと、テーブルとビューを選択するダイアログが表示される。
このダイアログが強烈に使いにくい。テーブル数が数百、数千とあるような大規模なシステムになると地獄を味わう。

テーブルとビューが一覧表示されるリストに、５行と半分ぐらいしかテーブルが表示されない。ダイアログのサイズは固定されていて、大きさを変えることもできない。ちょっとずつスクロールしながら１行ずつ設定していかなければならない。
「すべて選択」「すべて選択解除」というボタンがある。これをクリックするとすべての行のチェックボックスが変化する。あくまですべての行を一気に変えるものなので、部分的に変更したい場合は、ひとつひとつクリックしていく。ShiftキーやCtrlキーによる複数行選択も効かない。本当に一行、一行、設定していかなければならない。マウス操作ではチェックボックスの細かいクリックが辛いのでキーボードで行おうとすると、チェックのオン／オフはスペースキーでできるものの、次の行に移動しようと下矢印キーを押すと、２列目のセルがコンボボックスになっているので、コンボボックスの中身がスクロールしてしまう。
唯一の方法は、はじめにすべて選択にした後、チェックを解除したい行をクリックしてアクティブにし、スペースキーでその行のチェックを解除する、解除することにより２列目のコンボボックスが消える。それで下矢印キーで行移動が効くようになるので、次の行に移動できる。以降はスペースキーと下矢印キーを交互に連打する。選択しなければならない行よりも、選択しない行の方が多くても、マウスクリックでチェックするよりはキーボード連打の方が、幾分、速くて楽だ。
スペースキーと下矢印キー、２つのキーをがんばって連打しつづけて複数行の選択状態を変えることはできても数百行ともなると、かなり辛い。いや、辛かった。

各テーブルやビューには所有者がある。作成先はデフォルトで「dbo」になる。これまた一行ずつ、書き換えていかなければならない。
あらかじめクリップボードに所有者名をコピーしておいて、作成先のセルをクリックし、所有者名の位置までカーソル移動して、Ctrl+Vで貼り付け。次の行をクリックしては、カーソル移動して貼り付け。これを何百行と行わなければならない。いや、行った。

セルを編集状態から抜けようとしたり、なにかの拍子に、間違ってESCキーを押したりすれば、瞬時にダイアログが閉じて、行った編集が全部パーになる。いや、なった。
なったら、またひとつひとつやりなおしだ。涙が出る。

何時間もかけて、「次へ」ボタンを押す段階まで到達できたなら、次の画面でDTSパッケージとして保存しておくことができる。保存しなかったら、せっかく設定した内容は一回限りで終わるので注意したい。
保存したパッケージは、後から編集しなおすことができない。一度作ったら最後、先ほどの画面のテーブルとビューの選択を編集しなおすことはできない。一箇所でも間違っていれば、はじめからやりなおしとなる。

作ったDTSパッケージをちゃんと作って保存できたなら、「データ変換サービス」に登録されているので、そこから実行できる。実行にはかなり時間がかかる。

それで、苦労して作ったDTSパッケージを実行してみると、エラーの嵐・・・
もうエラーの内容を検証する気にもならず、あきらめて他の代替案で行った。代替案があったからいいけど。

これに限らず、SQL Server Enterprise Managerは、何をやっても反応が遅いし、どうにも使いにくい。

SQL Serverで現在の年月日をYYYYMMDD形式で作る

技術的なメモです。
SQL Serverのテーブルに文字列型で日付を格納しているような場合に、20111231といったようなYYYYMMDD形式にしていることがあります。
それらの中から今日以前といった条件で取得するために、現在の日付からYYYYMMDD形式の文字列を作りたい時があります。
以下の構文で作れます。

CONVERT(varchar, GETDATE(), 112)

例えば
select CONVERT(varchar, GETDATE(), 112)
とすると
20111122
等と返されます。