SQL Serverで複合連番を自動採番する：年＋シリアル／SqlServer

仕事でSQLServerを扱っていたのですが、久しく触っていなかったので大分忘れてしまいました。

単純な自動採番であれば該当列をint系に割り振り、プロパティの「IDENTITYの指定」で(IDである)を「はい」にすればいいのですが、年＋シリアル(連番)のような組み合わせだとそう簡単にいきません。

また、SQLで自動採番というとmax関数の利用を思いつきますが、年＋シリアル(連番)となると、こちらも簡単ではありません。

と言いつつも、変数を利用すると比較的簡単に構成できました。

年(2桁)＋シリアル(4桁)を取り扱うため、2014年は「140001」～「149999」の範囲です。シリアル途中で年が切り替わると(2015年になると)「150001」から再スタートさせます。

declare @MAX varchar(6)
select @MAX = max(連番) from [テーブル名] where year(発行日) = year(getdate())
set @MAX = case when
    @MAX is null then
      right(datename(yy, getdate()),2)+'0001'
    else
      @MAX+1 end
insert into [テーブル名] (連番,カラム1,カラム2) values (@MAX,データ1,データ2);

簡単な流れですが、

変数@MAXは6桁(年2桁+シリアル4桁)を確保します。
select文で現在の最大値を取得します。
未採番、もしくは今年分が未採番であれば西暦年4桁の下2桁＋シリアル初期値(0001)とします
採番済みであれば、現在地に1を加算します。
レコードを登録します。

となります。

同じ連番を複数のレコードに同時登録したい場合は

declare @MAX varchar(6)
select @MAX = max(連番) from [テーブル名] where year(発行日) = year(getdate())
set @MAX = case when
    @MAX is null then
      right(datename(yy, getdate()),2)+'0001'
    else
      @MAX+1 end
insert into [テーブル名] (連番,カラム1,カラム2) values (@MAX,データ1-1,データ2-1),(@MAX,データ1-2,データ2-2),(@MAX,データ1-3,データ2-3);

とvaluesを増やすことにより対応できます。

h3 class=”memo”>
使用したSQL構文／関数のメモ

declare 変数型：変数を指定の型で宣言します。
set 変数=値：変数に値を代入します。
case when 条件 then 真処理 else 偽処理 end：SqlServer上での条件分岐です。
year()：サーバー日付の年(int)を返します。
getdate()：サーバーの日付(datetime)を返します
datename()：datetime型から指定した表現の文字列を返します。「yy」は年を意味します。
right()：文字列の右側から指定した長さの文字列を返します。