Multitable Insert簡介

  假如我們有表sales，結構如下：

 

Prodid	Prodname	Mon_Amt	Tue_Amt	Wed_Amt	Thu_Amt	Fri_Amt	Sat_Amt
101 102	AIWA AKAI	2000 1900	2500 2100	2230 2130	2900 3100	3000 2800	2100 2120

 

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

 

Prodid	Prodname	WeekDay	Amount
101 101 101 101 101 101 102 102 102 102 102 102	AIWA AIWA AIWA AIWA AIWA AIWA AKAI AKAI AKAI AKAI AKAI AKAI	Mon Tue Wed Thu Fri Sat Mon Tue Wed Thu Fri Sat	2000 2500 2230 2900 3000 2100 1900 2100 2130 3100 2800 2120

 

  為了達到上面的效果，我們採用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;