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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-680908/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-680908/