iT邦幫忙

2018 iT 邦幫忙鐵人賽
DAY 8
0
Data Technology

SQL Server 學習日誌系列 第 8

08.使用 INSERT INTO … SELECT 輸入不重複資料

前言

這幾個月遇到一個需求:在某一個 Stored Procedure 需要把 Temp Table 資料寫入某一個資料表,但 ID重複的資料不寫入。寫入的語法為 INSERT SELECT,而重點在過濾資料的部分。一開始想到的作法是 NOT EXISTS 語法,但因為不理解,擔心有效能上的問題,於是在 Stack Overflow 上找到相關文章,發現其實有多種寫法,也有專家分析其優劣。


介紹

舉個簡單的例子,我們有資料表 A 與 B:
Table_A

Id Name
1 AA
2 BB
3 CC

Table_B

Id Name
1 PP

需求是將資料表A 的資料 放入資料表B,但不放入 ID 重複的資料。如果不過濾資料直接放入,我們的語法大概會長這樣

INSERT INTO TABLE_B
  (Id, Name)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta

而我們會有三種方法
NOT EXISTS

INSERT INTO TABLE_B (Id, name)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta
 WHERE NOT EXISTS ( SELECT Id
                      FROM TABLE_B tb
                     WHERE tb.Id = t1.Id )

NOT IN

INSERT INTO TABLE_B (idIdName)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta
 WHERE ta.Id NOT IN (SELECT Id
                      FROM TABLE_B)

LEFT JOIN/IS NULL

INSERT INTO TABLE_B  (Id, Name)
   SELECT ta.Id,
          ta.Name
     FROM TABLE_A ta
LEFT JOIN TABLE_B tb ON t2.id = t1.id
    WHERE t2.Id IS NULL

一看到 LEFT JOIN + IS NULL,就覺得這方法好像不錯,但其實專家們不推薦使用。若欄位皆為 非NULL 的情況下 NOT EXISTS 與 NOT IN 執行效率接近,且比起 LEFT JOIN + IS NULL 快上3倍。
而如果有複合鍵 NOT EXISTS 會特別有效 (可以點選參考資料2,看一下詳細比較)。

看看 Stack Overflow 找到解決方法時,別忘了看一下專家解釋,如果直接複製貼上可能會出糗;如果有回答不錯的,別忘了幫忙投票一下 :D


參考資料

  1. Stack Overflow: https://stackoverflow.com/questions/2513174/avoid-duplicates-in-insert-into-select-query-in-sql-server
  2. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server - EXPLAIN EXTENDED https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

偷偷分享一下自己的資料 /images/emoticon/emoticon07.gif


上一篇
07. 透過 Visual Studio Code 連接MS SQL Server
下一篇
13. Database Mail 設定
系列文
SQL Server 學習日誌30

尚未有邦友留言

立即登入留言