【SQL Server】よく忘れる構文備忘録

たまに使用する際に毎回調べて書いていたので、備忘録＆サンプルとして。

ストアドの実行結果をINSERT

ストアドの結果セットが一つであれば、INSERT INTO Table_name SELECT…と同じ要領で記述できます。

INSERT INTO Table_name 
	EXEC sp_lock

他のテーブルのデータでテーブルを更新する

UPDATE FROMを使用して他のテーブルの値で更新する構文です。

-- 事前準備
CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('002'	,'a002'	,'y000002')
	,('003'	,'a003'	,'y000003');

準備したデータは以下のとおり。

[Table_x]

Column1	Column2	Column3
001	a001	x000001
002	a002	x000002
003	a003	x000003

[Table_y]

Column1	Column2	Column3
001	a001	y000001
002	a002	y000002
003	a003	y000003

Table_xのColumn1が001であるものについて、Table_xのColumn3をTable_yのColumn3の値で更新してみます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM  [Table_y]
WHERE [Table_x].[Column2] = [Table_x].[Column2]
  AND [Table_x].[Column1] = '001'

f:id:utiowa:20170515030647p:plain

INNER JOINを組み合わせることもできます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM       [Table_x] 
INNER JOIN [Table_y]
	ON [Table_x].[Column2] = [Table_x].[Column2]
WHERE [Table_x].[Column1] = '001'

UPDATE対象のデータの処理前、処理後を出力する

SET句の後ろにOUTPUT句で記述します。

UPDATE [dbo].[Table_x]
SET Column2 = 'b001'
OUTPUT   deleted.*
	,inserted.*
WHERE Column3 IN ('x000001','x000003');

一時テーブルが存在するならば、削除する

一時テーブルにデータを作成⇒データ格納をしながら繰り返し試行錯誤する際に便利です。

IF OBJECT_ID(N'tempdb..#temp_TBL', N'U') IS NOT NULL
    DROP TABLE #temp_TBL;

一時テーブルが存在するならば、削除する【SQL Server2016】

簡潔に書くことができます。

DROP TABLE IF EXISTS tempdb..#temp_TBL;

テーブルが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.objects
	WHERE object_id = OBJECT_ID(N'Table_name')
	)
　　DROP TABLE Table_name;

テーブルが存在するならば、削除する【SQL Server2016】

とてもシンプル。見やすく書きやすい。

DROP TABLE IF EXISTS Table_name;

カラムが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	　AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
	ALTER TABLE [dbo].[Table_name] DROP COLUMN [Column_name] 
GO

カラムが存在するならば、削除する【SQL Server2016】

こちらもシンプルに記述できて便利です。

ALTER TABLE [dbo].[Table_name] DROP COLUMN IF EXISTS [Column_name] 
GO

DROP xxx IF EXISTSは、PROCEDUREやFUNCTION等にも使えるため、様々なDDL文に使用できます。

カラムが存在しないならば、追加する

単に削除の反対ですが。

IF NOT EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	　　　AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
　　ALTER TABLE [dbo].[Table_name] ADD [Column_name] VARCHAR(50)
GO

新規ならINSERT、存在するならUPDATE処理をする

データ更新時に行が存在すればUPDATE、新規であればINSERTをする場合はMRGER構文を使います。（ただし、SQL Server2008以降）
さらに処理した行の結果をOUTPUT句にて出力します。

-- 事前準備
DROP TABLE IF EXISTS Table_x;
DROP TABLE IF EXISTS Table_y;

CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('003'	,'a003'	,'y000003')
	,('004'	,'a001'	,'y000004')
	,('005'	,'a002'	,'y000002')
	,('006'	,'a003'	,'y000006');

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加してみます。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  OUTPUT deleted.*, inserted.*, $action;

f:id:utiowa:20170515030310p:plain

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加し、条件にあてはまらないTable_yの行を削除する場合。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  WHEN NOT MATCHED BY Source THEN
      DELETE 
  OUTPUT deleted.*, inserted.*, $action;