ITコーディネータのシュウです。

お久しぶりです。この写真は2か月前くらいに、島根の実家に帰省する際に乗った、寝台特急「サンライズ出雲」の、ノビノビ座席に乗ったときに撮ったものです。寝台車というと、個室を思い浮かべますが、最近は頭の部分だけが仕切られていて、そんなにそれぞれのスペースは広くはないですが、カーペットの座敷で横になってしっかり寝れるようなタイプの格安の席があります。
ちょうど、高速バスがコロナ禍で運行していない時期で、帰省する手段を探したところ見つかり、寝台車に久しぶりに乗りましたが、横になって休める分、バスよりもゆっくり眠れるような気がします。

最近再びコロナの感染者が増えてきており、様々心配は尽きませんが、こういうときこそ絆を大切にし、励ましあいながら、頑張って乗り越えて行きたいものです。
コロナウィルスを始め、様々なことがあったこの1年もいよいよ終わろうとしています。皆様も健康に気を付けてよいお年をお迎えください。

＜本日の題材＞
SEQUENCE(シーケンス)の現在値の変更（SQL Server）

以前、SEQUENCEについてブログで取り上げましたが、SEQUENCEの現在の値を修正したいというケースが発生する場合があります。例えば、主キーの値をシーケンスを使って採番するようにしていたが、データのメンテナンスが必要になり、データをコピーして追加したところ、主キーの値がシーケンスの値と大きくかけ離れた大きな値になってしまったというようなケースです。私も実際に複数のテーブルでそういうケースにぶつかってしまい、一つずつ、シーケンスの設定を変更しなければいけないという状況が発生しました。このとき、テーブルの主キーの値の最大値をSQLで確認した後、SQL ServerManagement Studioで１つ１つのシーケンスの現在値を変更していきましたが、もっとスムーズに設定の変更ができる方法がないかを探したときに、対応した方法です。

例)
「testA」テーブルの主キー「id」(int型)をSEQUENCE「test_seqA」を使って採番するかたちにします。
100番までSEQUENCEを使って採番した後、データのメンテナンスが発生して、120番までデータを追加しましたが、そのときにはSEQUENCEを使って採番しなかったので、現在値が100のままになっているので、そのままでは次にSEQUENCEを使ってデータを登録すると、主キーの重複でエラーになってしまいます。ですので、SEQUENCEの現在の値を120に修正する必要があります。

まず、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

100という結果が出ました。

次に、テーブル「testA」の主キー「id」の最大値を確認してみます。

SELECT MAX(id) FROM testA;

120という値が確認されました。

SEQUENCEの現在の値を120に修正する方法の1つは、ALTER SEQUENCE文で修正を行う方法です。

ALTER SEQUENCE test_seqA
RESTART WITH 120
INCREMENT BY 1;

（ちなみに、SQL Server Management Studioでシーケンスのプロパティで、「シーケンスの再開」から変更することも可能です）

上記の作業を順に行えば修正はできますが、毎回、いちいち上記の処理を順番に行うのも面倒なので、今回は、上記のALTER SEQUENCE文を使用するのではなく、下記のようなSQL文を作成して、それを実行するかたちにします。

DECLARE
@id_MAX   INT
,@Seq_cur_val   sql_variant
,@count         INT = 0;

SELECT @id_MAX = MAX(id)
FROM testA;

SELECT @Seq_cur_val = CURRENT_VALUE FROM sys.sequences
WHERE name = ‘test_seqA’;

IF @id_MAX > CAST(@Seq_cur_val AS INT)
BEGIN
WHILE @count < @id_MAX
BEGIN
              SELECT @count = NEXT VALUE FOR test_seqA;
END;
END;

実行します。

実行後、SEQUENCE「test_seqA」の現在の値を確認してみます。

SELECT current_value FROM sys.sequences WHERE name = 'test_seqA';

 

現在値が120に変更されているのが確認できます。
この状態であれば、「tetA」テーブルにSEQUENCE「test_seqA」を使って主キーを採番することが可能です。次の採番データを登録して見ます。

INSERT INTO testA(id) VALUES(NEXT VALUE FOR test_seqA);

データは登録されました。データの主キーの値が大きいものから10件確認してみます。

SELECT TOP 10 * FROM testA
ORDER BY id DESC;

正しく採番されていることが確認できました。
ちなみに、dbSheetClientでは、上記のSQLのテーブル名、主キー名、SEQUENCE名などを、Excelのシートから値を渡すことができますので、上記のSQLを実行するタスクを1つ作成して、それを各テーブルで必要なタイミングで呼び出すようにすることで、複数のテーブルにおいてもSEQUENCEの現在値を修正することが可能です。これを使うことで、何とか問題なく処理をすることが可能になり、とても助かりました。

今日は以上まで

にほんブログ村