// Net planet-es V2

SQLのCRUDで基本となる、 INSERT ,UPDATE, DELETE のテンプレート文をメモとして記載します。SQL Server 2005 で使用できるようになったOUTPUT 句も使用してみます。 OUTPUT 句は、INSERT,UPDATE,DELETEされた行情報を取得できる強力な機能です。

OUTPUT句を使用すると、INSERT処理時にIDENTITY列やNEWID()で新規に挿入した列の値を行のセットとして取得できます。ADO.NETで新規挿入した行のキー情報を返すようにすると、SCOPE_IDENTITY()などを使用せずに生成されたキー列の値を高速に取得できるようになります。

動作確認は、 SQL Server 2005 Developer Edition で行っています。

1. INSERT, UPDTE, DELETE の基本構文

INSERT, UPDATE ,DELETE のテンプレート文を掲載します。実際はCTE(共通テーブル式)と絡めて使用したりTOP句を使用するなど、複雑なクエリ式を使用することができます。正確な文法はBooks Online を参照してください。ここで紹介するのは、基本的なSQL ステートメントです。

OUTPUT 句の例は後半で説明しています。

1.1 INSERT

INSERT は値を VALUE 句で指定する方法と、 INSERT SELECT 文を使用する方法がよく使用されます。SELECT の代わりに、EXECUTE 文を使用して取得した行セットをテーブルにINSERTする方法もあります。後で記載しますが、OUTPUT句を使用すると挿入された行情報結果セットを返すことができるようになります。SELECT INTO 文で選択した結果を列が一致するテーブルに退避することもできます。

-- INSERT VALUES文の基本, [カラムリスト]とINTOはオプション
INSERT INTO テーブル名 [(カラムリスト)]
     VALUES([DEFAULT|NULL|式]のリスト)

-- INSERT SELECT文.WHEREで行を絞ることもできます
INSERT INTO テーブル名 [(カラムリスト)]
SELECT (カラムリスト)
  FROM ソーステーブル名

-- INSERT ストアドプロシージャ
INSERT INTO テーブル名 [(カラムリスト)]
  EXEC ストアドプロシージャ

1.2 UPDATE

WHERE 条件に一致した列を更新します。WHERE条件が指定されない場合は、すべての行の列が指定された値で更新されます。FROM 句をつけて、テーブルをINNER JOIN, OUTER JOIN などで結合することで、複雑な条件に一致する行のみを更新するようにもできます。FROM 句を使用した例はOUTPUTの説明のサンプルSQLで確認してください。 UPDATE もINSE

-- UPDATE
UPDATE テーブル名
  SET 更新カラム = [式|DEFAULT|NULL]
     (,更新カラム = [式|DEFAULT|NULL] ..)
 WHERE 検索条件

-- UPDATE で FROMを使用して、検索条件を複雑にする
UPDATE テーブル名
  SET 更新カラム = [式|DEFAULT|NULL]
     (,更新カラム = [式|DEFAULT|NULL] ..)
  FROM テーブル名 (INNER JOIN ..など)
 WHERE 検索条件

1.3 DELETE

DELETEはWHERE 句に一致する行を削除します。truncate を使用すると高速にすべてのテーブルの行を削除できます。 UPDATE と同様にFROM句を使用して、テーブルを結合することで、複雑な条件で削除行を指定することができるようになります。

-- DELETE
DELETE FROM テーブル名
 WHERE 検索条件

-- 高速にテーブルの全レコード削除
TRUNCATE TABLE テーブル名

2. OUTPUT 句を使用する

SQL Server 2005 から、INSERT, UPDATE, DELETE でOUTPUT 句を使用すると影響を受けた行セットを取得できるようになります。OUTPUT区では、INSERTの場合は、insertedでインサートされた行情報,DELETE では、deleted で削除行情報を取得できるようになります。UPDATE では、inserted, deleted を使用して変更前、後の行情報を取得できます。

サンプルではProductテーブルを作成して、行の追加、更新、削除時に影響を受けた行情報を返すようにしています。

-- SQL Server 2005 からはINSERT,UPDATE,DELETEで
-- OUTPUT 句が使用できます。これは、INSERT,UPDATE,DELETE
-- で影響の受けが行データが返り値となります。
USE SampleDB
GO

IF OBJECT_ID('[dbo].[Product]') IS NOT NULL
  DROP TABLE [dbo].[Product]
GO
-- サンプル用のテーブル定義
CREATE TABLE [dbo].[Product](
	[ProductID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Price] [money] NOT NULL,
	[CreatedON] [datetime] NOT NULL CONSTRAINT [DF_Product_CreatedON]  DEFAULT (getdate()),
	[ModifiedOn] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedOn]  DEFAULT (getdate()),
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)
)

-- 挿入された新規行を取得
INSERT INTO dbo.Product(ProductID, Name, Price)
     OUTPUT inserted.*
     VALUES (NEWID(), 'suportsbike', 10000)

-- 挿入された新規行の特定の列を取得
INSERT INTO dbo.Product(ProductID, Name, Price)
     OUTPUT inserted.ProductID, inserted.Name
     VALUES (NEWID(), 'bike', 10000)

INSERT INTO dbo.Product(ProductID, Name, Price)
     OUTPUT inserted.ProductID, inserted.Name
     VALUES (NEWID(), 'bike', 20000)

-- 削除された行を取得
DELETE FROM dbo.Product
     OUTPUT deleted.ProductID, deleted.Name
      WHERE NAME = 'bike'

-- 更新された行の新旧の値を取得
UPDATE dbo.Product
   SET Price = Price * 2
OUTPUT inserted.ProductID, deleted.Price, inserted.Price
 WHERE Name = 'suportsbike'

-- UPDATE,DELETE に FROM テーブル名 を使用するサンプル
-- UPDATEのFROM句指定版
UPDATE dbo.Product
   SET Price = Price * 2
OUTPUT inserted.ProductID, deleted.Price, inserted.Price
  FROM dbo.Product
 WHERE Name = 'suportsbike'

-- 削除された行を取得,FROM句指定版
DELETE FROM dbo.Product
     OUTPUT deleted.ProductID, deleted.Name
       FROM dbo.Product
      WHERE NAME = 'bike'

3. まとめ

今回の説明は以上です。 INSERT SELECT 文とか SELECT INSERT の順番を忘れたりするので、備忘録として記載しました。また、OUTPUT 句は非常に便利にもかかわらず知られていなかったりするので、本記事に記載しました。

間違い、指摘点などがありましたらご連絡ください。