用 T-SQL 將 TableA 的資料新增到資料表 TableB

使用 SQL 語法INSERT...SELECT

可以使用 INSERT...SELECT 語句來達成這個目的，並搭配 NOT EXISTS 或 LEFT JOIN 來判斷 Table_B 中是否已存在相同的記錄。

1. 使用 NOT EXISTS

這是最直接且易於理解的方法。它會檢查 Table_B 中是否不存在與 Table_A 中料號相同的記錄，如果不存在，才會進行插入。

SQL
INSERT INTO Table_B (料號, 欄位1, 欄位2, ...)
SELECT T1.料號, T1.欄位1, T1.欄位2, ...
FROM Table_A AS T1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table_B AS T2
    WHERE T2.料號 = T1.料號
);

2. 使用 LEFT JOIN

這個方法透過將 Table_A 和 Table_B 進行 LEFT JOIN，然後篩選出在 Table_B 中沒有對應記錄的資料（即 T2.料號 IS NULL）。

SQL
INSERT INTO Table_B (料號, 欄位1, 欄位2, ...)
SELECT T1.料號, T1.欄位1, T1.欄位2, ...
FROM Table_A AS T1
LEFT JOIN Table_B AS T2 ON T1.料號 = T2.料號
WHERE T2.料號 IS NULL;

效能考量

  • NOT EXISTS 通常在處理大量資料時，效能會比 LEFT JOIN 好一些，因為一旦找到符合條件的記錄，它就會停止掃描。
  • LEFT JOIN 則需要將整個表進行連接，然後再進行過濾。

使用 SQL 語法 MERGE

MERGE 語法是一個功能強大且靈活的 T-SQL 語句，它能同時處理 INSERT、UPDATE、DELETE 等多種操作，非常適合用來同步兩個資料表的資料

SQL
MERGE Table_B AS target
USING Table_A AS source ON (target.料號 = source.料號)
WHEN NOT MATCHED BY target THEN
    INSERT (料號, 欄位1, 欄位2, ...)
    VALUES (source.料號, source.欄位1, source.欄位2, ...);

語法說明

  • MERGE Table_B AS target：指定目標資料表（Table_B）。
  • USING Table_A AS source：指定來源資料表（Table_A）。
  • ON (target.料號 = source.料號)：這是連結兩個資料表的條件，這裡我們使用 料號 作為識別碼。
  • WHEN NOT MATCHED BY target THEN：這是一個條件判斷式。當來源資料表（Table_A）中的記錄，在目標資料表（Table_B）中沒有找到匹配的記錄時，就會執行這個區塊內的指令。
  • INSERT...VALUES：執行插入操作，將來源資料（source）的欄位值，插入到目標資料表（target）中。

MERGE 的優點

  • 語法簡潔：一個語句就可以處理多種操作，可讀性高。
  • 效能優化：MSSQL 引擎在處理 MERGE 語句時，會對來源和目標資料表進行一次性的掃描，通常比分開執行 SELECT、INSERT 語句更有效率。
  • 功能強大：除了 WHEN NOT MATCHED BY TARGET 之外，你還可以加入其他條件，例如：
    • WHEN MATCHED THEN UPDATE ...：當兩表有匹配記錄時，執行更新操作。
    • WHEN NOT MATCHED BY SOURCE THEN DELETE ...：當目標表中有些記錄在來源表中不存在時，執行刪除操作。

在 source 加入篩選條件

要使用 MERGE 語法並在 source（來源資料表）中加入篩選條件，你只需要在 USING 子句後面，將 Table_A 替換成一個包含篩選條件的 SELECT 語句即可。

這個 SELECT 語句會成為 MERGE 的「來源資料集」，只有符合條件的資料才會參與後續的 ON 判斷和 WHEN 條件的處理。

語法範例

假設你只想插入 Table_A 中 數量 > 100 的料號，你可以這樣寫：

SQL
MERGE Table_B AS target
USING (
    SELECT 料號, 欄位1, 欄位2, 數量
    FROM Table_A
    WHERE 數量 > 100 -- 在這裡加入你的篩選條件
) AS source ON (target.料號 = source.料號)
WHEN NOT MATCHED BY target THEN
    INSERT (料號, 欄位1, 欄位2, 數量)
    VALUES (source.料號, source.欄位1, source.欄位2, source.數量);

範例說明

  1. USING ( ... ) AS source：這是一個子查詢（Subquery），它會先執行 SELECT 語句，並將結果集視為 MERGE 的來源資料。
  2. WHERE 數量 > 100：這就是你加入的篩選條件。
  3. MERGE 語法接下來的操作（ON 和 WHEN NOT MATCHED...）只會針對這個經篩選後的資料集來進行。

透過這種方式，你可以精準地控制哪些來自 Table_A 的資料可以參與 MERGE 的處理，讓你的 T-SQL 程式碼更具彈性和效率。

使用 SQL 語法 CURSOR FOR

這個方法會逐一讀取 Table_A 的每一筆資料，並在迴圈內判斷 Table_B 是否已存在該筆料號，如果沒有，就執行插入。

SQL
DECLARE @料號 VARCHAR(50);
DECLARE @欄位1 VARCHAR(50);
DECLARE @欄位2 VARCHAR(50);
-- 宣告其他需要的變數

DECLARE cur CURSOR FOR
    SELECT 料號, 欄位1, 欄位2 -- 選擇你需要從 Table_A 讀取的欄位
    FROM Table_A;

OPEN cur;

FETCH NEXT FROM cur INTO @料號, @欄位1, @欄位2;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 判斷 Table_B 是否已存在此筆料號
    IF NOT EXISTS (SELECT 1 FROM Table_B WHERE 料號 = @料號)
    BEGIN
        -- 如果不存在，則執行插入
        INSERT INTO Table_B (料號, 欄位1, 欄位2)
        VALUES (@料號, @欄位1, @欄位2);
    END

    -- 讀取下一筆資料
    FETCH NEXT FROM cur INTO @料號, @欄位1, @欄位2;
END

CLOSE cur;
DEALLOCATE cur;

語法說明

  1. DECLARE 變數：首先，你需要宣告對應 Table_A 中欄位的變數，用來暫存從游標中讀取出來的值。
  2. DECLARE cur CURSOR FOR：宣告一個名為 cur 的游標，並定義它要從 Table_A 中讀取哪些資料。
  3. OPEN cur：打開游標，準備開始讀取資料。
  4. FETCH NEXT FROM cur INTO ...：從游標中讀取第一筆資料，並將其值存入你所宣告的變數中。
  5. WHILE @@FETCH_STATUS = 0：這是一個迴圈。只要 FETCH 動作成功（@@FETCH_STATUS 等於 0），就會持續執行迴圈內的程式碼。
  6. IF NOT EXISTS (...)：在迴圈內部，使用 NOT EXISTS 查詢來檢查 Table_B 中是否已存在相同的料號。
  7. INSERT INTO ... VALUES (...)：如果判斷結果為 TRUE，則執行插入操作。
  8. FETCH NEXT FROM cur INTO ...：在迴圈的結尾，讀取下一筆資料，以便進行下一輪的判斷。
  9. CLOSE cur 和 DEALLOCATE cur：在迴圈結束後，關閉並釋放游標佔用的資源。

重要提醒

雖然 CURSOR 可以實現這個功能，但在 SQL Server 中，CURSOR 通常被認為是一種低效能的方法。因為它會逐一處理每一筆資料，而非一次性處理整個集合（set-based operation）。

在實際應用中，通常會更推薦使用 INSERT...SELECT、MERGE 或是 UPSERT（如果你的資料庫支援的話），因為這些方法在處理大量資料時，效能會比 CURSOR 好上許多。CURSOR 比較適合用在需要對每筆資料進行複雜邏輯處理，而無法用單一集合操作完成的特殊情況。

附註說明

SQL
--需求:將Table_A的資料塞到Table_B，並把Table_B產生的ID編號回寫回Table_A的對應欄位 ```sql
-- 宣告變數
DECLARE @para1 VARCHAR(50)
DECLARE @para2 uniqueidentifier
DECLARE @para3 VARCHAR(100)

--宣告指標變數
DECLARE _cursor CURSOR FOR
SELECT s1,s2,s3 FROM Table_A where eiplstatus = 1

--啟用指標變數
OPEN _cursor
--以指標變數為基準點, 將取出的欄位值,依序塞給我們自訂的變數，亦即將每筆s1 => @para1、 s2 => @para2 、s3 => @para3
FETCH NEXT FROM _cursor INTO @para1,@para2,@para3

WHILE @@FETCH_STATUS = 0
BEGIN
insert into Table_B(c1,c2,c3) values(@para1,@para2,@para3)
SET @id = SCOPE_IDENTITY();

update Table_A set id = @id where CURRENT of _cursor

-- 指標變數往下移動, 並將取出的欄位值, 設定給我們自訂的變數
FETCH NEXT FROM _cursor INTO @para1,@para2,@para3
END

--關閉指標變數
CLOSE _cursor
--釋放指標變數
DEALLOCATE _cursor

查詢資料庫內所有資料表的欄位

SQL
/*
  [資料庫名稱] : TestDB
*/

USE TestDB;

SELECT 
    a.TABLE_SCHEMA + '.' + a.TABLE_NAME   as '表格名稱'
   ,b.COLUMN_NAME                         as '欄位名稱'
   ,b.DATA_TYPE                           as '資料型別'   
   ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as '長度'   
   ,isnull(b.COLUMN_DEFAULT,'')           as '預設值'   
   ,b.IS_NULLABLE                         as '是否允許空值'   
   ,( SELECT value   
      FROM fn_listextendedproperty (
              NULL            ,'schema'
             ,a.TABLE_SCHEMA  ,'table'
             ,a.TABLE_NAME    ,'column'
             ,default
           )   
      WHERE 
           name       ='MS_Description' 
       AND objtype    ='COLUMN'    
       AND objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
  )                                       as '欄位描述' 
FROM 
  INFORMATION_SCHEMA.TABLES  a   
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b 
  ON a.TABLE_NAME = b.TABLE_NAME 
WHERE 
      a.TABLE_TYPE    = 'BASE TABLE'
  AND a.TABLE_NAME    Like '%%'  -- 資料表名稱
  AND b.COLUMN_NAME   Like '%%'  -- 資料表欄位名稱
ORDER BY 
  a.TABLE_NAME , b.ORDINAL_POSITION

相關文章

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *


目前趨勢

PoorMansTSqlFormatter in SSMS v20
用 T-SQL 將 TableA 的資料新增到資料表 TableB
受保護的內容: 鼎新ERP批號溯源（Lot Tracing）逆向查詢SQL
清除SQL Server Log檔 (交易紀錄)

內容目錄

← 內容索引