【SQL Server】テーブルの特定の列の値を変更してレコードをコピーする(INSERT)

SQL Serverであるテーブルのレコードを同テーブルにコピーしつつ、特定の列の値を変更したいという処理がありました。

ネットで調べていると、INSERT INTOとSELECTを使用するという方法が出てきたのでこれを使用していますが、途中エラーなどが出て少しハマったので記録しておきます。

やりたいこと

以下のようなテーブルがあるとします。レストランの予約管理システムか何かだと思ってください。

DataIdはIDENTITY列で、自動でインクリメントして自動採番できる列です。（重要）

DataIdの2,3を見ると、同じ顧客IDが並んでいます。予約がキャンセルされた場合、予約番号は同じものを使用し枝番管理する仕組みです。ある予約に関するキャンセル履歴を残すためこのようなレコードを持つ仕様です。

同じように、DataIdが4の顧客ID003の方が予約をキャンセルし、また別日に予約しなおした場合は以下のテーブルとなります。

以下のコードで実現できました

色々な方法がありますが、今回は

①コピー元レコードをコピーして新しいレコードとしてインサートする

②インサートした新レコードのうち書き換え必要な項目をUPDATEで書き換える

③コピー元レコードのうち書き換え必要な項目をUPDATEで書き換える

上記の手順でコピーしていきます。

前提として、コピー元となるDataIdは事前に取得されており、特定されています。以下コードでは分かりやすいように「DataId=4」と指定していますが、多くの場合は変数に格納されていると思うので適宜読み替えてください。

INSERT INTO tb_booking
     SELECT
          予約番号,
          予約番号枝番,
          顧客ID,
          予約状況,
          予約処理日
     FROM tb_booking
     WHERE DataId=4

DECLARE @BEFORE_ID int
SET @BEFORE_ID = @@IDENTITY;

UPDATE tb_booking
SET 予約番号枝番=予約番号枝番+1
予約処理日=FORMAT(GETDATE(),'yyyy/MM/dd')
WHERE DataId=@BEFORE_ID"

UPDATE tb_booking
SET 予約状況='キャンセル'
WHERE DataId=4

コード解説

①コピー元レコードをコピーして新しいレコードとしてインサートする

INSERT INTO tb_booking
     SELECT
          予約番号,
          予約番号枝番,
          顧客ID,
          予約状況,
          予約処理日
     FROM tb_booking
     WHERE DataId=4

「INSERT INTO テーブル名」でテーブルに新しいレコードを追加しています。

SELECT以下で指定した結果をINSERTする、という構造です。

前述の通り、コピー元となるレコードのDataIdは事前に取得済みである前提です。ここでは分かりやすく「DataId=4」としていますが多くの場合ここは変数になるかと思います。

SELECTの条件として「WHERE DataId=4」を指定することで、DataId=4のレコードがSELECTされ、新しいレコードとしてINSERTされます。

②インサートした新レコードのうち書き換え必要な項目をUPDATEで書き換える

DECLARE @BEFORE_ID int
SET @BEFORE_ID = @@IDENTITY;

SQLで変数を使用する際、DECLAREを使って宣言します。VB.NETでいうところのDim です。以下のように使用します。

DECLARE @変数名 変数の型

DECLARE @変数名 変数の型 = 初期値

私が書いたサンプルコードでは「@BEFORE_ID」という変数をint型で宣言しています。

変数に値を代入する際はSETを使います。

SET @変数名 = 設定したい値

私が書いたサンプルコードでは「@BEFORE_ID」に@@IDENTITYを代入しています。

@@IDENTITYとはSQL Serverで用意されている関数で、直前のINSERTで自動採番したIDENTITY列の値を返すシステム関数です。

つまり、DataID=4のレコードをコピー＆インサートしたレコードのDataIdを取得して変数に代入しています。

UPDATE tb_booking
SET 予約番号枝番=予約番号枝番+1
予約処理日=FORMAT(GETDATE(),'yyyy/MM/dd')
WHERE DataId=@BEFORE_ID

@BEFORE_IDのDataIdのレコードに対して、予約番号枝番をインクリメントしてセット、予約処理日をその時点の日付を取得してセットしてUPDATEをかけています。これでインサートしたレコードの処理はOKです。

③コピー元レコードのうち書き換え必要な項目をUPDATEで書き換える

UPDATE tb_booking
SET 予約状況='キャンセル'
WHERE DataId=4

最後にコピー元レコードの予約状況の列を「予約完了」→「キャンセル」にUPDATEしています。

想定されるエラーの解消方法

コピー元レコードをコピーしてINSERTするとき、テーブルの列が多いがために一つ一つ列名を書き出すのが面倒で以下のようにSELECTの項目を「*」にしたい場合もあると思います。

INSERT INTO tb_booking
     SELECT *
     FROM tb_booking
     WHERE DataId=4

ただこれはエラーとなります。以下のようなエラーメッセージが出るかと思います。これは全項目をSELECTすると「DataId」まで含まれてしまうためです。

DataIdはIDENTITY列で、この項目は自動でインクリメントして採番されるので、ここに明示的な値が入ったレコードはINSERTできませんよ、ということを言っています。言い換えると、自動発番を設定したカラムはいじることができない、という制約です。

ですので面倒ですが、DataID以外のコピーしたい列名を全て書き出してやる必要があります。

なお、調べていると以下の方法でIDENTITY_INSERT をONに変更することでそのままINSERTが可能なようです。

SET IDENTITY_INSERT tb_booking ON;

ただIDENTITY列を自動発番列と設定した以上、手動でいじるのはトラブルの元なので個人的にはあまりおすすめしません。

以上です。

参考サイト

以下のサイトを参考にさせていただいています。ありがとうございます。

・直前のINSERTで自動採番したIDENTITY列の値を取得する

・表をSELECTして別の表へINSERTする（INSERT ～ SELECT）

・SET IDENTITY_INSERT (Transact-SQL)