2021-01-26

SQL : WITH RECURSIVE ... INSERT INTO ...

SQL : WITH RECURSIVE ... INSERT INTO ...

目標:
  1. 先建立一個空資料表格tabWith,有兩個資料欄位 score INT PRIMARY KEY, grade NVARCHAR(120)
  2. 用WITH RECURSIVE的方式,插入五筆資料:(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')
作法:
  1. 測試環境的資料庫,可以參閱以下網址連結來建立:
  2. 選取要作業的資料庫對象(TestWind),開啟(SQL Editor):Tools → Open SQL Editor
  3. 在Query分頁中輸入所要建立測試資料表tabWith的執行指令
    CREATE TABLE tabWith (
        score   INTEGER        NOT NULL,
        grade   NVARCHAR(120),
        PRIMARY KEY (score)
    );
    

  4. 執行SQL指令:(F9) Execute SQL
  5. Status : 確認SQL指令 CREATE TABLE tabWith 執行無誤


  6. 用WITH RECURSIVE的方式,插入五筆資料:(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')
    在Query分頁中輸入WITH RECURSIVE INSERT INTO的SQL指令
    WITH  RECURSIVE cte AS (
        SELECT 1 AS c1, 'a' AS c2
        UNION ALL
        SELECT c1 + 1, c2 || 'a' 
        FROM cte
        WHERE c1 < 5
    ) 
    INSERT INTO tabWith (score, grade) SELECT c1, c2 FROM cte;
    
  7. 執行SQL指令:(F9) Execute SQL,Status : 確認SQL指令WITH RECURSIVE INSERT INTO 執行無誤