テーブル型変数とMERGE文を使って複数データをUPSERTする

MultiUpsert.sql

/* 「複数データのUPSERT」
 * 作成者：Kenta Yamada
 * 作成日：2014/09/09
 * DBMS:SQL-Server2008R2
 */

--データベース作成
CREATE TABLE sqlPractice


--テーブル作成
GO
USE sqlPractice

GO
CREATE TABLE products (
     id          INT         NOT NULL
    ,name        VARCHAR(40) NOT NULL
    ,category_id CHAR(6)     NOT NULL
    ,upd_id      CHAR(6)     NOT NULL
    ,upd_date    DATETIME    NOT NULL
    ,PRIMARY KEY(id)
)


--ユーザー定義テーブル型作成
GO
CREATE TYPE Products AS TABLE (
     id          INT
    ,name        VARCHAR(40)
    ,category_id CHAR(6)
    ,upd_id      CHAR(6)
)


--productsテーブルに１件データ挿入、確認
GO
INSERT INTO products
  VALUES (1, 'ham egg', '000001', 'test', GETDATE())

SELECT * FROM products


--ストアドプロシージャ定義
GO
CREATE PROCEDURE EntryProducts (
    @products Products readonly
) AS
BEGIN TRY
  BEGIN TRAN
    MERGE INTO products
      USING (SELECT * FROM @products) t
        ON products.id = t.id
      WHEN MATCHES then
        UPDATE SET
           products.name = t.name
          ,products.category_id = t.category_id
          ,products.upd_id = t.upd_id
          ,upd_date =getdate()
      WHEN NOT MATCHED THEN
        INSERT INTO VALUES (
           (SELECT MAX(id) + 1 FROM products)
          ,t.name
          ,t.category_id
          ,t.upd_id
          ,GETDATE()
        );
  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK TRAN
  SELECT ERROR_MESSAGE()
END CATCH

GO
--プロシージャ実行テスト
SELECT * FROM products

DECLARE @table Products

INSERT INTO @table
  VALUES (1, 'ham toast', '000001', 'test', GETDATE())
        ,(2, 'steake', '000001', 'test', GETDATE())

EXEC EntryProducts @table

--プロシージャ実行後データ確認
SELECT * FROM products