【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'
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;
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;