Multitable Insert簡介

  現在，我打算把 SALES 表中的行，增加到 Week_Sales 表中，增加後的表資料結構如下：

  為了達到上面的效果，我們採用Multi table INSERT的語法方式進行插入，語句如下：

Insert all

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Mon’,mon_amt)

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Tue’,tue_amt)

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Wed’,wed_amt)

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Thu’,thu_amt)

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Fri’,fri_amt)

     Into week_sales(prodid,prodname,weekday,amount)

     Values (prodid,prodname,’Sat’,sat_amt)

  Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt

          Fri_amt,sat_amt from sales;

  不但可以插入同一個表，也可以插入到不同表，例如如下的指令碼，同時插入suppliers表和customers表中：

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;